package com.iris.live.services.common.kpi;

/**
 * KPI工具类
 */
@Deprecated
public class KPIStatements {
    //新车外部销售收入（OK）， 当月开票销售收入 - 当月退票销售收入 (CHECKED)
    public static String newCarSaleAmount = " SELECT 'LSH' 集团, a.大区, a.大区_EN, a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA,if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.许可_LSH, a.经销商名,a.经销商名_EN, " +
            " b.来源, b.品牌, b.级别,  新车销量, (车辆销售价未税 - 消费税未税) 新车外部销售收入 " +
            " FROM LSH经销商表 a LEFT JOIN ( " +
            "     SELECT a.许可, a.来源, a.品牌, a.级别, " +
            "            SUM(TYPE *(车辆销售价未税)) 车辆销售价未税,  " +
            "            SUM(TYPE *(消费税未税)) 消费税未税, " +
            "            SUM(TYPE) 新车销量 " +
            "     FROM (" +
            "         SELECT a.许可, b.来源, B.品牌, B.级别, " +
            "             CASE " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND  " +
            "                      DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0 " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1 " +
            "                 WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1 " +
            "             END TYPE, " +
            "             IFNULL(a.折后价, 0) / 1.17 AS 车辆销售价未税, " +
            "             IFNULL(a.消费税,0) / 1.17 AS 消费税未税 " +
            "         FROM vw_retailed_quotation a, LSH车型 b,  报价订单表 c " +
            "	      WHERE a.订单编号 = c.订单编号 and LEFT(c.销售类型,1) NOT IN ('B', 'D', 'I', 'P') " +
            "              AND  ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "	           OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01'))) " +
            "              AND (CASE WHEN 'MB' = '' THEN TRUE ELSE b.品牌 = 'MB' END)  " +
            "              AND a.车型编号 = b.车型编号 " +
            "    ) a " +
            "    GROUP BY a.许可, a.来源, a.品牌, a.级别 " +
            " ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'";

    // BMBS销售目标完成率(BMBS RT TA OK) (CHECKED)
    public static String bmbs = "SELECT allset.*, IFNULL(a.新车销量,0) as BMBS_新车销量, IFNULL(a.新车销量,0) as 新车销量, IFNULL(b.bmbs_销售目标,0) as bmbs_销售目标 FROM  " +
            " kpi_allset allset " +
            "LEFT JOIN " +
            "( " +
            "   SELECT  a.`所属经销商` AS permit,c.来源,c.品牌,c.级别,COUNT(1) AS 新车销量 " +
            "   FROM  lsh库存表 a,  库存利润表 b,  lsh车型 c " +
            "   WHERE  b.`上报厂家` = '是' " +
            "      AND DATE_FORMAT(b.`上报厂家日期`,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d')  " +
            "      AND DATE_FORMAT(b.`上报厂家日期`,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') " +
            "      AND a.`库存编号` = b.`库存编号` " +
            "      AND a.`车型编号` = c.`车型编号` " +
            "      AND a.is_delete = 0 " +
            "      AND a.`车架号` NOT LIKE 'IRIS%' " +
            "      AND (CASE WHEN 'MB' = '' THEN TRUE ELSE c.品牌 = 'MB' END) " +
            "   GROUP BY a.`所属经销商`,c.来源,c.品牌,c.级别 " +
            " ) a   " +
            "ON a.permit=allset.许可_BMBS AND a.`来源`=allset.`来源` AND a.`品牌`=allset.`品牌` AND a.`级别`=allset.`级别` " +
            "LEFT JOIN  " +
            "( " +
            "    SELECT permit,b.`来源`,b.`品牌`,b.`级别`, sum(IFNULL(bmbs_ta,0)) as bmbs_销售目标 " +
            "    FROM snp_setting a    " +
            "    join LSH车型 b on a.carCode = b.车型编号    " +
            "    WHERE y_m >= concat(substr('2000-01-01', 1, 4), substr('2000-01-01', 6, 2))    " +
            "    AND y_m <= concat(substr('2020-01-01', 1, 4), substr('2020-01-01', 6, 2))    " +
            "    and (case when 'MB' = '' then true else b.品牌 = 'MB' end)   " +
            "    group by a.permit,b.`来源`,b.`品牌`,b.`级别` " +
            ") b  " +
            "ON b.permit=allset.许可_BMBS AND b.`来源`=allset.`来源` AND b.`品牌`=allset.`品牌` AND b.`级别`=allset.`级别`";

    // LSH销售目标完成率 = 开票数-退票数-开票bug造成的多余数据 (OK) (CHECKED)
    public static String lsh = "SELECT allset.*, IFNULL(a.新车销量,0) as 新车销量, IFNULL(b.lsh_销售目标,0) as lsh_销售目标 " +
            " FROM  kpi_allset allset " +
            " LEFT JOIN ( " +
            "           SELECT  " +
            "               aa.`许可`, dd.来源, dd.品牌, dd.级别,SUM(num) 新车销量 " +
            "           FROM ( " +
            "               SELECT " +
            "                   quote.`初始报价单号`,quote.`许可`,quote.`车型编号`,COUNT(DISTINCT retail.`开票时间`) num " +
            "               FROM " +
            "                   `报价单表` quote, " +
            "                   `报价交车表` retail, " +
            "                   `报价订单表` quote_order " +
            "               WHERE quote.`交车` = retail.`编号` " +
            "               AND quote.`订单编号` = quote_order.`订单编号` " +
            "               AND quote.`是否还原点` = '是' " +
            "               AND quote.`是否驳回` = '否' " +
            "               AND retail.`开票时间`>=CONCAT('2000-01-01',' 00:00:01') " +
            "               AND retail.`开票时间`<=CONCAT('2020-01-01',' 23:59:59') " +
            "               AND LEFT(quote_order.`销售类型`,1) NOT IN ('B', 'I') " +
            "               GROUP BY quote.`初始报价单号` " +
            "               UNION ALL " +
            "               SELECT " +
            "                   quote.`初始报价单号`,quote.`许可`,quote.`车型编号`,-COUNT(DISTINCT retail.`退票时间`) num " +
            "               FROM " +
            "                   `报价单表` quote, " +
            "                   `报价交车表` retail, " +
            "                   `报价订单表` quote_order " +
            "               WHERE quote.`交车` = retail.`编号` " +
            "               AND quote.`订单编号` = quote_order.`订单编号` " +
            "               AND quote.`是否还原点` = '是' " +
            "               AND quote.`是否驳回` = '否' " +
            "               AND retail.`退票时间`>=CONCAT('2000-01-01',' 00:00:01') " +
            "               AND retail.`退票时间`<=CONCAT('2020-01-01',' 23:59:59') " +
            "               AND LEFT(quote_order.`销售类型`,1) NOT IN ('B', 'I') " +
            "               GROUP BY quote.`初始报价单号` " +
            "               UNION ALL " +
            "               SELECT  " +
            "                   bb.`初始报价单号`,bb.`许可`,bb.`车型编号`, -COUNT(*) num " +
            "               FROM ( " +
            "                   SELECT " +
            "                       MAX(b.`报价编号`) qid, a.`开票时间` " +
            "                   FROM " +
            "                   ( " +
            "                       SELECT " +
            "                           quote.`报价编号` , quote.`许可` , quote.`初始报价单号` , retail.`开票时间` " +
            "                       FROM " +
            "                           `报价单表` quote , " +
            "                           `报价交车表` retail , " +
            "                           `报价订单表` quote_order " +
            "                       WHERE " +
            "                           quote.`交车` = retail.`编号` " +
            "                       AND quote.`订单编号` = quote_order.`订单编号` " +
            "                       AND quote.`是否还原点` = '是' " +
            "                       AND quote.`是否驳回` = '否' " +
            "                       AND retail.`开票时间` >= CONCAT('2000-01-01' , ' 00:00:01') " +
            "                       AND retail.`开票时间` <= CONCAT('2020-01-01' , ' 23:59:59') " +
            "                       AND LEFT(quote_order.`销售类型`,1) NOT IN ('B', 'I') " +
            "                       AND ISNULL(retail.`交车日期`) " +
            "                       AND (ISNULL(retail.`退票时间`) OR (NOT ISNULL(retail.`退票时间`) AND quote.carTypeChange=1 AND quote_order.订单状态='开票C')) " +
            "                       AND ISNULL(retail.`换票时间`) " +
            "                   ) a " +
            "                   JOIN `报价单表` b ON a.`许可` = b.`许可` " +
            "                   AND a.`初始报价单号` = b.`初始报价单号` " +
            "                   AND b.`是否还原点` = '是' " +
            "                   AND b.`是否驳回` = '否' " +
            "                   AND b.`报价编号` < a.`报价编号` " +
            "                   GROUP BY a.`报价编号` " +
            "               ) aa, " +
            "               `报价单表` bb , " +
            "               `报价交车表` cc " +
            "               WHERE " +
            "                   aa.qid = bb.`报价编号` " +
            "               AND bb.`交车` = cc.`编号` " +
            "               AND NOT ISNULL(cc.`开票时间`) " +
            "               AND ISNULL(cc.`退票时间`) " +
            "               AND ISNULL(cc.`换票时间`) " +
            "               AND ISNULL(cc.`交车日期`)" +
            "               AND aa.`开票时间`!=cc.`开票时间` " +
            "               GROUP BY bb.`初始报价单号` " +
            "           ) aa , " +
            "           `lsh车型` dd  " +
            "           WHERE " +
            "           aa.`车型编号` = dd.`车型编号` " +
            "           AND (CASE WHEN 'MB' = '' THEN TRUE ELSE dd.品牌 = 'MB' END)  " +
            "           GROUP BY aa.`许可`, dd.来源, dd.品牌, dd.级别 " +
            ") a   " +
            "ON a.`许可`=allset.`许可_BMBS` AND a.`来源`=allset.`来源` AND a.`品牌`=allset.`品牌` AND a.`级别`=allset.`级别` " +
            "LEFT JOIN  " +
            "( " +
            "             SELECT permit,b.`来源`,b.`品牌`,b.`级别`, sum(IFNULL(lsh_ta,0)) as lsh_销售目标 " +
            "             FROM snp_setting a    " +
            "             join LSH车型 b on a.carCode = b.车型编号 and y_m <> '190001'    " +
            "             WHERE y_m >= concat(substr('2000-01-01', 1, 4), substr('2000-01-01', 6, 2))    " +
            "             AND y_m <= concat(substr('2020-01-01', 1, 4), substr('2020-01-01', 6, 2))    " +
            "             and (case when 'MB' = '' then true else b.品牌 = 'MB' end)   " +
            "               group by a.permit,b.`来源`,b.`品牌`,b.`级别` " +
            ") b  " +
            "ON b.permit=allset.许可_BMBS AND b.`来源`=allset.`来源` AND b.`品牌`=allset.`品牌` AND b.`级别`=allset.`级别`";

    // GP1（OK）(CHECKED)
    public static String salesGP1 = " SELECT 'LSH' 集团, a.大区, a.大区_EN, a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA,if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
            " a.salesCode, b.来源, b.品牌, b.级别,  GP1, 新车外部销售收入 " +
            " FROM LSH经销商表 a LEFT JOIN ( " +
            "   SELECT a.许可, a.来源, a.品牌, a.级别, " +
            "          SUM(TYPE * (a.车辆销售价未税 - a.消费税未税 - a.采购价格未税)) GP1, " +
            "          SUM(TYPE * (IF(LEFT(a.销售类型,1) IN ('B', 'D', 'I', 'P'), 0, a.车辆销售价未税 - a.消费税未税))) 新车外部销售收入    " +
            "   FROM (" +
            "	       SELECT  a.许可, b.来源, b.品牌, b.级别, c.销售类型, " +
            "              CASE " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND  " +
            "                      DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0 " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1 " +
            "                 WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1 " +
            "              END TYPE, " +
            "              IFNULL(a.折后价, 0) / 1.17 AS 车辆销售价未税, " +
            "              IFNULL(a.消费税,0) / 1.17 AS 消费税未税, " +
            "              case when a.所属经销商 = a.调入经销商 then ifnull(a.协商调车款项金额, 0)  " +
            "                   else IFNULL(a.采购价格含税, 0) end /1.17 采购价格未税 " +
            "          FROM vw_retailed_quotation a, LSH车型 b,  报价订单表 c " +
            "	       WHERE  a.订单编号 = c.订单编号 and ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "	           OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01'))) " +
            "             AND (CASE WHEN 'MB' = '' THEN TRUE ELSE b.品牌 = 'MB' END)  " +
            "             AND a.车型编号 = b.车型编号 " +
            "   ) a " +
            "   GROUP BY a.许可, a.来源, 品牌, a.级别 " +
            " ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'";

    //销售毛利GP1.5（OK）(CHECKED)
    public static String salesGP1Dot5 = " SELECT 'LSH' 集团, a.大区, a.大区_EN, a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA,if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
            "  a.salesCode, a.来源,a.品牌, a.级别, " +
            " SUM(VEHICLE_GP_1DOT5.TYPE * (VEHICLE_GP_1DOT5.车辆销售价未税 - VEHICLE_GP_1DOT5.消费税未税 - VEHICLE_GP_1DOT5.采购价格未税 - (经销商赠送成本未税 + 延保赠送成本未税 + 精品赠送成本未税))) - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.精品赠送成本调整差,0)/1.17 - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.五星延保赠送成本调整差,0)/1.17  GP1dot5, " +
            " SUM(VEHICLE_GP_1DOT5.TYPE * (IF(LEFT(VEHICLE_GP_1DOT5.销售类型,1) IN ('B', 'D', 'I', 'P'), 0, VEHICLE_GP_1DOT5.车辆销售价未税 - VEHICLE_GP_1DOT5.消费税未税))) 新车外部销售收入, " +
            " (0- IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.精品赠送成本调整差,0)/1.17 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.五星延保赠送成本调整差,0)/1.17)  品牌调整值 " +
            " FROM kpi_allset a " +
            " LEFT JOIN ( " +
            "         SELECT a.许可, b.来源, b.品牌, b.级别, c.销售类型, " +
            "             CASE " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND  " +
            "                      DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0 " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1 " +
            "                 WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1 " +
            "             END TYPE, " +
            "             IFNULL(a.折后价,0)/1.17 AS 车辆销售价未税, " +
            "             IFNULL(a.消费税,0)/1.17 AS 消费税未税, " +
            "		      case when a.所属经销商 = a.调入经销商 then ifnull(a.协商调车款项金额, 0)   " +
            "                  else IFNULL(a.采购价格含税, 0) end /1.17 AS 采购价格未税, " +
            "		      IFNULL(e.经销商赠送成本, 0) AS 经销商赠送成本未税,    " +
            "		      IFNULL(g.延保赠送成本, 0) / 1.17  + IFNULL(VEHICLE_INCOME_ADJUSTMENT.五星延保赠送成本调整差, 0) / 1.17 AS 延保赠送成本未税,    " +
            "		      IFNULL(l.精品赠送成本, 0) / 1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.精品赠送成本调整差, 0)/1.17 AS 精品赠送成本未税    " +
            "	      FROM vw_retailed_quotation a  " +
            "	      LEFT JOIN (      " +
            "		       SELECT 延保编号, IFNULL(延保原价,0) AS 延保赠送成本    " +
            "		       FROM 报价五星延保 WHERE 延保折后价 = 0      " +
            "	      ) g ON g.延保编号 = a.延保编号      " +
            "	      LEFT JOIN (      " +
            "		       SELECT  编号, sum(成本 * 数量) 精品赠送成本 " +
            "              FROM 报价精品详细表 WHERE 折后价 = 0 GROUP BY  编号 " +
            "	      ) l ON l.编号 = a.精品      " +
            "	      LEFT JOIN (    " +
            "		      SELECT SUM(随车成本) AS 经销商赠送成本, 报价编号 FROM 报价随车表      " +
            "		      WHERE 随车类型 = '经销商' AND 随车部门='销售部' GROUP BY 报价编号    " +
            "	      ) e ON e.报价编号 = a.报价编号" +
            "         LEFT JOIN (" +
            "             SELECT STOCK_CODE, SUM(after_market_product_cost_adjust) 精品赠送成本调整差, SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust, 0)) 五星延保赠送成本调整差 " +
            "             FROM INCOME_ADJUSTMENT " +
            "             WHERE TYPE = 1 GROUP BY STOCK_CODE " +
            "         ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号 , " +
            "	      LSH车型 b, 报价订单表 c    " +
            "	      WHERE  a.订单编号 = c.订单编号 and ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "	          OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01'))) " +
            "            AND (CASE WHEN 'MB' = '' THEN TRUE ELSE b.品牌 = 'MB' END)  " +
            "            AND a.车型编号 = b.车型编号 " +
            "   ) VEHICLE_GP_1DOT5 ON a.许可_BMBS = VEHICLE_GP_1DOT5.许可 AND a.来源 = VEHICLE_GP_1DOT5.来源 AND a.品牌 = VEHICLE_GP_1DOT5.品牌 AND  a.级别 = VEHICLE_GP_1DOT5.级别  " +
            "   LEFT JOIN (" +
            "         SELECT PERMIT,  来源, 品牌, 级别, SUM(after_market_product_cost_adjust) 精品赠送成本调整差, SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust, 0)) 五星延保赠送成本调整差  " +
            "         FROM INCOME_ADJUSTMENT, LSH车型 " +
            "         WHERE INCOME_ADJUSTMENT.TYPE = 2 AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
            "         AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "         AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m')" +
            "         AND '2000-01-01' <> '2020-01-01' " +
            "         GROUP BY PERMIT, 来源, 品牌, 级别 " +
            "   ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND" +
            "     a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
            "   LEFT JOIN (" +
            "         SELECT PERMIT,  来源, 品牌, SUM(after_market_product_cost_adjust) 精品赠送成本调整差 , SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust, 0)) 五星延保赠送成本调整差 " +
            "         FROM INCOME_ADJUSTMENT, LSH车型 " +
            "         WHERE INCOME_ADJUSTMENT.TYPE = 2 AND CAR_CODE = 车型编号 AND (级别 IS NULL OR 级别 ='') " +
            "         AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "         AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m')" +
            "         AND '2000-01-01' <> '2020-01-01' " +
            "         GROUP BY PERMIT, 来源, 品牌 " +
            "   ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
            " WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
            " GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";

    //销售毛利GP2（OK）(CHECKED)
    public static String salesGP2 = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN,  if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA,if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
            " a.salesCode, a.来源,a.品牌, a.级别,  " +
            " SUM(VEHICLE_GP_2.TYPE * (VEHICLE_GP_2.车辆销售价未税 - VEHICLE_GP_2.消费税未税 -VEHICLE_GP_2.采购价格未税 - (经销商赠送成本未税 + 延保赠送成本未税 + 精品赠送成本未税  + 厂家随车赠送成本 +  贴息金额)))  " +
            " - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.精品赠送成本调整差,0)/1.17 " +
            " - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.延保赠送成本调整差,0)/1.17 " +
            " - IFNULL(BMBS_VEHICLE_MODEL_调整值.厂家随车赠送成本调整值,0) - IFNULL(BMBS_VEHICLE_MODEL_调整值.车辆贴息调整值,0) GP2, " +
            " -IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.精品赠送成本调整差,0)/1.17 " +
            " -IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.延保赠送成本调整差,0)/1.17 " +
            " - IFNULL(BMBS_VEHICLE_BRAND_调整值.厂家随车赠送成本调整值,0) + " +
            " + IFNULL(BMBS_VEHICLE_BRAND_调整值.车辆贴息调整值,0) 品牌调整值, " +
            " SUM(VEHICLE_GP_2.TYPE * (IF(LEFT(VEHICLE_GP_2.销售类型,1) IN ('B', 'D', 'I', 'P'), 0, VEHICLE_GP_2.车辆销售价未税 - VEHICLE_GP_2.消费税未税))) 新车外部销售收入 " +
            " FROM kpi_allset a " +
            " LEFT JOIN ( " +
            "         SELECT a.许可, 来源, g.品牌, g.级别, h.销售类型," +
            "             CASE " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND  " +
            "                      DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0 " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1 " +
            "                 WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1 " +
            "             END TYPE, " +
            "             IFNULL(a.折后价,0)/1.17 AS 车辆销售价未税, " +
            "             IFNULL(a.消费税,0) / 1.17 AS 消费税未税,      " +
            "		      case when a.所属经销商 = a.调入经销商 then ifnull(a.协商调车款项金额, 0)   " +
            "                  else IFNULL(a.采购价格含税, 0) end /1.17 AS 采购价格未税, " +
            "		      IFNULL(c.经销商赠送成本, 0) AS 经销商赠送成本未税,      " +
            "             IFNULL(GET_BMBS_AMOUNT(A.报价编号, A.库存编号),0) AS 厂家随车赠送成本, " +
            "		      IFNULL(e.延保赠送成本, 0) / 1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.延保赠送成本调整差, 0) / 1.17 AS 延保赠送成本未税,    " +
            "		      IFNULL(f.精品赠送成本, 0) / 1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.精品赠送成本调整差, 0) / 1.17 AS 精品赠送成本未税,    " +
            "		      IFNULL(BMBS_调整值.车辆贴息调整值, IFNULL(lsh金融申请附属.贴息金额, 0)) AS 贴息金额    " +
            "	      FROM vw_retailed_quotation a  " +
            "         LEFT JOIN ( " +
            "             SELECT 初始报价单号, SUM(IFNULL(贴息金额, 0)) 贴息金额 " +
            "             FROM lsh金融申请附属 g " +
            "             GROUP BY 初始报价单号  " +
            "         ) lsh金融申请附属 ON a.初始报价单号 = lsh金融申请附属.初始报价单号 " +
            "	      LEFT JOIN (      " +
            "		      SELECT SUM(随车成本) AS 经销商赠送成本, 报价编号 FROM 报价随车表      " +
            "		      WHERE 随车类型 = '经销商' AND 随车部门 = '销售部' GROUP BY 报价编号      " +
            "	      ) c ON c.报价编号 = a.报价编号      " +
            "	      LEFT JOIN (      " +
            "		      SELECT 延保编号, IFNULL(延保原价,0) AS 延保赠送成本    " +
            "		      FROM 报价五星延保 WHERE 延保折后价 = 0   " +
            "	      ) e ON e.延保编号 = a.延保编号      " +
            "	      LEFT JOIN (      " +
            "		      SELECT  编号, sum(成本 * 数量) 精品赠送成本 " +
            "             FROM 报价精品详细表 WHERE   折后价 = 0 GROUP BY  编号" +
            "	      ) f ON f.编号 = a.精品      " +
            "         LEFT JOIN (" +
            "             SELECT A.STOCK_ID, SUM(IF(A.SI_BMBS_CODE='F', A.SI_AMOUNT, 0)) 车辆贴息调整值  " +
            "             FROM SI_BMBS_SETTING A " +
            "             WHERE A.SET_TYPE = 1" +
            "             GROUP BY STOCK_ID " +
            "         ) BMBS_调整值 ON BMBS_调整值.STOCK_ID = a.库存编号  " +
            "         LEFT JOIN (" +
            "             SELECT STOCK_CODE, SUM(after_market_product_cost_adjust) 精品赠送成本调整差, SUM(IF(IS_GIVE = 1, five_stars_extend_cost_adjust, 0)) 延保赠送成本调整差 " +
            "             FROM INCOME_ADJUSTMENT " +
            "             WHERE TYPE = 1 GROUP BY STOCK_CODE " +
            "         ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号 , " +
            "	     LSH车型 g, 报价订单表 h    " +
            "	      WHERE  a.订单编号 = h.订单编号 and  ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "	          OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01'))) " +
            "            AND (CASE WHEN 'MB' = '' THEN TRUE ELSE g.品牌 = 'MB' END)  " +
            "            AND a.车型编号 = g.车型编号 " +
            "   ) VEHICLE_GP_2 ON  a.许可_BMBS = VEHICLE_GP_2.许可 AND a.来源 = VEHICLE_GP_2.来源 AND a.品牌 = VEHICLE_GP_2.品牌 AND  a.级别 = VEHICLE_GP_2.级别  " +
            "   LEFT JOIN (" +
            "         SELECT PERMIT,  来源, 品牌, 级别, SUM(after_market_product_cost_adjust) 精品赠送成本调整差, SUM(IF(IS_GIVE = 1, five_stars_extend_cost_adjust, 0)) 延保赠送成本调整差  " +
            "         FROM INCOME_ADJUSTMENT, LSH车型 " +
            "         WHERE INCOME_ADJUSTMENT.TYPE = 2 AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
            "         AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "         AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "         AND '2000-01-01' <> '2020-01-01' " +
            "         GROUP BY PERMIT, 来源, 品牌, 级别 " +
            "   ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND" +
            "     a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
            "   LEFT JOIN (" +
            "         SELECT PERMIT,  来源, 品牌, SUM(after_market_product_cost_adjust) 精品赠送成本调整差, SUM(IF(IS_GIVE = 1, five_stars_extend_cost_adjust, 0)) 延保赠送成本调整差  " +
            "         FROM INCOME_ADJUSTMENT, LSH车型 " +
            "         WHERE INCOME_ADJUSTMENT.TYPE = 2 AND CAR_CODE = 车型编号 AND (级别 IS NULL OR 级别 ='') " +
            "         AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "         AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "         AND '2000-01-01' <> '2020-01-01' " +
            "         GROUP BY PERMIT, 来源, 品牌 " +
            "   ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
            "   LEFT JOIN (" +
            "        SELECT PERMIT,  来源, 品牌, 级别, SUM(IF(A.SI_BMBS_CODE='F', 0 , A.SI_AMOUNT)) 厂家随车赠送成本调整值, " +
            "                           SUM(IF(A.SI_BMBS_CODE='F', A.SI_AMOUNT, 0)) 车辆贴息调整值  " +
            "        FROM SI_BMBS_SETTING A, SI_SETTING B, LSH随车项目表 C, LSH车型 " +
            "        WHERE A.SET_TYPE = 1 AND CAR_TYPE_ID = 车型编号 AND 车型 IS  NULL " +
            "             AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "             AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "             AND A.SI_SETTING_ID = B.ID AND B.si_name_cn = C.随车 AND C.类型 = '厂家' AND SI_BMBS_CODE <> 'PT'" +
            "             AND '2000-01-01' <> '2020-01-01' " +
            "        GROUP BY PERMIT,  来源, 品牌, 级别 " +
            "   ) BMBS_VEHICLE_MODEL_调整值 ON a.许可_BMBS = BMBS_VEHICLE_MODEL_调整值.PERMIT AND a.来源 = BMBS_VEHICLE_MODEL_调整值.来源 AND a.品牌 = BMBS_VEHICLE_MODEL_调整值.品牌 AND" +
            "     a.级别 = BMBS_VEHICLE_MODEL_调整值.级别 " +
            "   LEFT JOIN (" +
            "        SELECT PERMIT,  来源, 品牌, SUM(IF(A.SI_BMBS_CODE='F', 0 , A.SI_AMOUNT)) 厂家随车赠送成本调整值, " +
            "                           SUM(IF(A.SI_BMBS_CODE='F', A.SI_AMOUNT, 0)) 车辆贴息调整值  " +
            "        FROM SI_BMBS_SETTING A, SI_SETTING B, LSH随车项目表 C, LSH车型 " +
            "        WHERE A.SET_TYPE = 1 AND CAR_TYPE_ID = 车型编号 AND (级别 IS NULL OR 级别 ='') " +
            "             AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "             AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "             AND A.SI_SETTING_ID = B.ID AND B.si_name_cn = C.随车 AND C.类型 = '厂家' AND SI_BMBS_CODE <> 'PT'" +
            "             AND '2000-01-01' <> '2020-01-01' " +
            "        GROUP BY PERMIT,  来源, 品牌 " +
            "   ) BMBS_VEHICLE_BRAND_调整值 ON a.许可_BMBS = BMBS_VEHICLE_BRAND_调整值.PERMIT AND a.来源 = BMBS_VEHICLE_BRAND_调整值.来源 AND a.品牌 = BMBS_VEHICLE_BRAND_调整值.品牌   " +
            " WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
            " GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";

    // 二手车评估率(OK)
    public static String usedCarAppraisalRate = " SELECT 'LSH' 集团, a.大区, a.大区_EN, a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA,if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode, b.来源, b.品牌, b.级别, " +
            "   二手车评估台数, 进店线索数 " +
            "   FROM lsh经销商表 a  " +
            "   LEFT JOIN (  " +
            "       SELECT 许可, 来源, 品牌, 级别, SUM(二手车评估台数) 二手车评估台数, SUM(IF(顾客统计号 IS NULL OR 顾客统计号 = '0' , 0, 1)) 进店线索数 " +
            "       FROM ( " +
            "	       SELECT a.许可, b.来源, b.品牌, b.级别, " +
            "              1 二手车评估台数, 0 顾客统计号 " +
            "	        FROM vw_retailed_quotation a, LSH车型 b, 报价置换表 c " +
            "	       WHERE a.车型编号 = b.车型编号  and a.置换编号 = c.置换编号  AND c.置换估价 > 0 " +
            "	           AND date_format(a.`开票时间`,'%Y-%m-%d') >= '2000-01-01' and date_format(a.`开票时间`,'%Y-%m-%d') <= '2020-01-01'  AND  退票时间 IS NULL " +
            "              AND (CASE WHEN 'MB' = '' THEN TRUE ELSE B.品牌 = 'MB' END)  " +
            "          UNION ALL " +
            "	           SELECT a.许可, 来源, c.品牌, c.级别, 0 AS 二手车评估台数, IFNULL(b.`初始报价单号`, a.顾客编号) AS 顾客统计号    " +
            "	           FROM 顾客进店记录表 a " +
            "              LEFT JOIN 报价单表 b ON b.报价编号 = a.报价编号 " +
            "              LEFT JOIN 报价订单表 d ON b.订单编号 = d.订单编号, " +
            "              LSH车型 c     " +
            "	           WHERE a.`进店日期` >= '2000-01-01' and a.`进店日期` <= '2020-01-01'    " +
            "                AND (a.到店目的 IN ('看车比价','订单') OR (a.到店目的 IN ('办手续','交车') AND d.订单申请日期 IS NULL AND d.订单审批通过日期 IS NOT NULL)) " +
            "                AND left(a.到店时间,2) not in ('00','01','02','03') " +
            "                AND (CASE WHEN 'MB' = '' THEN TRUE ELSE C.品牌 = 'MB' END)  " +
            "                AND a.意向车型A = c.车型编号 " +
            "              GROUP BY  a.许可, 来源, c.品牌, 级别, 二手车评估台数, 顾客统计号 " +
            "       ) a GROUP BY 许可, 来源, 品牌, 级别 " +
            "  ) B ON a.许可_BMBS = b.许可   where a.`是否测试` = '否' ";

    //金融费用 单车金融手续费收入（OK）04金融战败  09金融通过后撤单, CHECKED
    public static String financialInfo = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
            "     a.salesCode, a.来源,a.品牌, a.级别,  " +
            "     SUM(VEHICLE_FINANCE_INFO.TYPE) 新车销量, " +
            "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融) 新车金融台数, " +
            "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融 * VEHICLE_FINANCE_INFO.金融手续费) + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust,0)/1.17 金融手续费 , " +
            "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融 * VEHICLE_FINANCE_INFO.金融服务费) 金融服务费,    " +
            "     SUM(VEHICLE_FINANCE_INFO.TYPE * VEHICLE_FINANCE_INFO.是否金融 * IFNULL(VEHICLE_FINANCE_INFO.贷款金额,0)) 贷款金额, " +
            "     IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.finance_poundage_adjust,0)/1.17 品牌调整值   " +
            " FROM kpi_allset a  " +
            " LEFT JOIN (" +
            "      SELECT  a.许可, d.来源,d.品牌,d.级别, " +
            "              CASE " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND  " +
            "                      DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0 " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1 " +
            "                 WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1 " +
            "              END TYPE, " +
            "              IF(C.状态 NOT IN ('04','09') AND B.金融机构 IS NOT NULL AND B.贷款额 > 0 , 1, 0) 是否金融, " +
            "              IFNULL(a.折后价,0) - IFNULL(b.首付额,0) 贷款金额, " +
            "              IFNULL(b.金融服务费,0)/1.06 金融服务费, " +
            "              (IFNULL(b.金融手续费,0) + IFNULL(e.finance_poundage_adjust,0))/1.17 金融手续费 " +
            "      FROM vw_retailed_quotation a " +
            "      LEFT JOIN (" +
            "               SELECT PERMIT, STOCK_CODE, FINANCE_POUNDAGE_ADJUST  " +
            "               FROM INCOME_ADJUSTMENT" +
            "               WHERE TYPE = 1 " +
            "      ) e ON A.许可 = e.permit and a.库存编号 = e.stock_code   " +
            "      LEFT JOIN 报价金融表 b ON a.`金融` = b.`金融编号`" +
            "      LEFT JOIN LSH金融申请 c ON a.初始报价单号 = c.初始报价单号, `lsh车型` d " +
            "      WHERE A.车型编号 = d.车型编号 " +
            "            AND ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "	          OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01'))) " +
            " ) VEHICLE_FINANCE_INFO ON a.许可_BMBS = VEHICLE_FINANCE_INFO.许可 AND a.来源 = VEHICLE_FINANCE_INFO.来源 AND a.品牌 = VEHICLE_FINANCE_INFO.品牌 AND  a.级别 = VEHICLE_FINANCE_INFO.级别 " +
            " LEFT JOIN (" +
            "      SELECT PERMIT, 来源, 品牌, 级别,   " +
            "             SUM(finance_poundage_adjust) finance_poundage_adjust " +
            "      FROM INCOME_ADJUSTMENT, LSH车型   " +
            "      WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
            "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
            "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "            AND '2000-01-01' <> '2020-01-01' " +
            "      GROUP BY PERMIT, 来源, 品牌, 级别  " +
            " ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
            " LEFT JOIN (" +
            "      SELECT PERMIT, 来源, 品牌,   " +
            "             SUM(finance_poundage_adjust) finance_poundage_adjust " +
            "      FROM INCOME_ADJUSTMENT, LSH车型   " +
            "      WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
            "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
            "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "            AND '2000-01-01' <> '2020-01-01' " +
            "      GROUP BY PERMIT, 来源, 品牌  " +
            " ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
            " WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
            " GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";

    //Agility渗透率 OR 先享后选渗透率(OK)
    public static String agilityPenetrationRate = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN," +
            "  a.salesCode, 来源, b.品牌, b.级别,    " +
            "  b.新车Agility台数, b.新车销量     " +
            "  FROM LSH经销商表 a  LEFT JOIN (  " +
            "     SELECT a.许可, 来源, a.品牌, a.级别,   " +
            "            SUM(TYPE * a.新车Agility台数) 新车Agility台数,  " +
            "            SUM(TYPE * a.新车台数) 新车销量  " +
            "     FROM (  " +
            "             SELECT  a.许可, 来源, d.品牌, d.级别, a.车型编号, a.开票时间,   " +
            "                     CASE   " +
            "                       WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND    " +
            "                            DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0   " +
            "                       WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1   " +
            "                       WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1   " +
            "                     END TYPE,   " +
            "                     1 新车台数,  " +
            "                     IF(C.状态 NOT IN ('04','09') && B.金融机构 ='奔驰租赁' AND B.贷款额 > 0, 1, 0) 新车Agility台数  " +
            "             FROM vw_retailed_quotation a    " +
            "             LEFT JOIN  报价金融表 b ON a.金融 = b.`金融编号`" +
            "             LEFT JOIN LSH金融申请 c ON a.初始报价单号 = c.初始报价单号,  LSH车型 d   " +
            "             WHERE a.车型编号 = d.车型编号   " +
            "               AND ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')      " +
            "               OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01')))   " +
            "               AND (CASE WHEN 'MB' = '' THEN TRUE ELSE d.品牌 = 'MB' END)    " +
            "     ) a   " +
            "     GROUP BY a.许可,来源, 品牌, a.级别   " +
            " ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否' ";

    // 进金融服务费收入（OK）04金融战败  09金融通过后撤单(CHECKED)
    @Deprecated
    public static String financingServiceFeePerUnit = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN," +
            " a.salesCode, 来源, b.品牌, b.级别,  " +
            " b.金融服务费收入 金融服务费, b.新车金融台数   " +
            " FROM LSH经销商表 a  LEFT JOIN (" +
            "   SELECT a.许可, 来源, a.品牌, a.级别, " +
            "          SUM(TYPE * 新车金融台数) 新车金融台数, " +
            "          SUM(TYPE * 金融服务费收入) 金融服务费收入    " +
            "   FROM (" +
            "	       SELECT  a.许可, 来源, d.品牌, d.级别, " +
            "              CASE " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND  " +
            "                      DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0 " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1 " +
            "                 WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1 " +
            "              END TYPE, " +
            "              1 新车金融台数, " +
            "              b.金融服务费/1.06 金融服务费收入 " +
            "          FROM vw_retailed_quotation a, 报价金融表 b, LSH金融申请 c, LSH车型 d " +
            "          WHERE a.`金融` = b.`金融编号` AND a.初始报价单号 = c.初始报价单号 and a.车型编号 = d.车型编号 " +
            "            AND ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "	          OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01'))) " +
            "            AND (CASE WHEN 'MB' = '' THEN TRUE ELSE d.品牌 = 'MB' END)  " +
            "            AND C.状态 NOT IN ('04','09')  " +
            "            AND B.金融机构 IS NOT NULL AND B.贷款额 > 0 " +
            "   ) a " +
            "   GROUP BY a.许可, 来源, 品牌, a.级别 " +
            " ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否' ";

    // 后市场产品毛利率 + 后市场产品单车营业额 (OK)(CHECKED)
    public static String afterMarketProductStatistics = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN," +
            " a.salesCode, a.来源,a.品牌, a.级别,  " +
            " SUM(VEHICLE_AFTERMARKET_INFO.TYPE * 新车销量) 新车销量, " +
            " SUM(VEHICLE_AFTERMARKET_INFO.TYPE * (车辆销售价未税 - 消费税未税)) 新车外部销售收入, " +
            " SUM(VEHICLE_AFTERMARKET_INFO.TYPE * 后市场产品营业额) 后市场产品营业额, " +
            " SUM(VEHICLE_AFTERMARKET_INFO.TYPE * (后市场产品营业额 - 后市场产品成本)) - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust, 0)/1.17 后市场产品销售毛利, " +
            " 0 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust, 0)/1.17 品牌后市场产品成本调整   " +
            " FROM kpi_allset a  " +
            " LEFT JOIN (" +
            "      SELECT  a.许可, c.来源, c.品牌, c.级别, " +
            "          CASE " +
            "            WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND  " +
            "                 DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0 " +
            "            WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1 " +
            "            WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1 " +
            "          END TYPE, " +
            "          1 新车销量, " +
            "          IFNULL(a.折后价,0)/1.17 AS 车辆销售价未税,      " +
            "          IFNULL(a.消费税,0) / 1.17 AS 消费税未税,      " +
            "          b.后市场产品营业额 / 1.17 后市场产品营业额, " +
            "          (b.后市场产品成本 / 1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust, 0)/1.17)  后市场产品成本 " +
            "      FROM vw_retailed_quotation a " +
            "      LEFT JOIN ( " +
            "		  SELECT 编号, SUM(IFNULL(`折后价` * 数量, 0)) 后市场产品营业额, SUM(成本* 数量) 后市场产品成本     " +
            "		  FROM 报价精品详细表 a WHERE 折后价 > 0 GROUP BY a.`编号`     " +
            "	   ) b ON b.编号 = a.精品  " +
            "      LEFT JOIN (  " +
            "         SELECT PERMIT, STOCK_CODE,   " +
            "                SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
            "         FROM INCOME_ADJUSTMENT   " +
            "         WHERE TYPE = 1  " +
            "         GROUP BY PERMIT, STOCK_CODE " +
            "      ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.PERMIT =a.许可 AND VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号  " +
            "      , LSH车型 c " +
            "      WHERE a.车型编号 = c.车型编号 " +
            "            AND ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "	          OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01'))) " +
            " ) VEHICLE_AFTERMARKET_INFO ON a.许可_BMBS = VEHICLE_AFTERMARKET_INFO.许可 AND a.来源 = VEHICLE_AFTERMARKET_INFO.来源 AND a.品牌 = VEHICLE_AFTERMARKET_INFO.品牌 AND  a.级别 = VEHICLE_AFTERMARKET_INFO.级别 " +
            " LEFT JOIN (" +
            "      SELECT PERMIT, 来源, 品牌, 级别,   " +
            "             SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
            "      FROM INCOME_ADJUSTMENT, LSH车型   " +
            "      WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
            "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
            "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "            AND '2000-01-01' <> '2020-01-01' " +
            "      GROUP BY PERMIT, 来源, 品牌, 级别  " +
            " ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
            " LEFT JOIN (" +
            "      SELECT PERMIT, 来源, 品牌,  " +
            "             SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
            "      FROM INCOME_ADJUSTMENT, LSH车型   " +
            "      WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
            "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
            "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "            AND '2000-01-01' <> '2020-01-01' " +
            "      GROUP BY PERMIT, 来源, 品牌  " +
            " ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
            " WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
            " GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";

    //SI金额（OK）  销售管理报表(CHECKED)
    public static String siMoney = "SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN," +
            "  a.salesCode, Z.来源,z.级别,z.品牌,  " +
            "  ROUND(z.SI,2) as SI金额, 品牌调整值  " +
            "  FROM LSH经销商表 a LEFT JOIN (      " +
            "             select          " +
            "                  SUM(type* (厂家随车SI + 贴息金额 + `零售SI` + `批发SI`))  " +
            "                  + IFNULL(VEHICLE_MODEL_BMBS_调整.随车调整值,0) " +
            "                  + IFNULL(VEHICLE_MODEL_贴息_调整.贴息调整值,0) " +
            "                  + IFNULL(VEHICLE_MODEL_SI_调整.R_SI_AMOUNT,0) " +
            "                  + IFNULL(VEHICLE_MODEL_SI_调整.W_SI_AMOUNT,0)  AS SI,        " +
            "                  IFNULL(VEHICLE_BRAND_BMBS_调整.随车调整值,0) " +
            "                  + IFNULL(VEHICLE_BRAND_贴息_调整.贴息调整值,0) " +
            "                  + IFNULL(VEHICLE_BRAND_SI_调整.R_SI_AMOUNT,0) " +
            "                  + IFNULL(VEHICLE_BRAND_SI_调整.W_SI_AMOUNT,0)  AS 品牌调整值,        " +
            "                  e.许可,e.来源, e.品牌, e.级别 " +
            "             from (  " +
            "                     SELECT       " +
            "                            IFNULL(IFNULL(VEHICLE_BMBS_调整值.随车调整值 ,f.厂家随车SI),0) AS 厂家随车SI,    " +
            "                            IFNULL(IFNULL(VEHICLE_贴息_调整值.贴息调整值,lsh金融申请附属.贴息金额),0) 贴息金额,  " +
            "                            IFNULL(IFNULL(IFNULL(VEHICLE_SI_调整.R_SI_AMOUNT,z.零售SI), snp_setting.预估零售SI),0) AS 零售SI,  " +
            "                            IFNULL(IF(b.所属经销商 = b.调入经销商, 0, IFNULL(IFNULL(VEHICLE_SI_调整.W_SI_AMOUNT, Z.`批发SI`), snp_setting.预估批售SI)),0) AS `批发SI`,  " +
            "                            b.许可, d.来源, d.品牌,d.级别 ,  " +
            "                            CASE   " +
            "                            WHEN date_format(b.开票时间,'%Y-%m-%d') >= '2000-01-01'  and date_format(b.开票时间,'%Y-%m-%d')  <= '2020-01-01'    " +
            "                                 AND  date_format(b.退票时间,'%Y-%m-%d') >= '2000-01-01' and date_format(b.退票时间,'%Y-%m-%d')  <= '2020-01-01' THEN 0   " +
            "                            WHEN date_format(b.开票时间,'%Y-%m-%d') >= '2000-01-01' and date_format(b.开票时间,'%Y-%m-%d') <='2020-01-01' THEN 1   " +
            "                            WHEN date_format(b.退票时间,'%Y-%m-%d') >= '2000-01-01' and  date_format(b.退票时间,'%Y-%m-%d') <= '2020-01-01' THEN -1   " +
            "                            END type  " +
            "                     FROM vw_retailed_quotation b    " +
            "                     LEFT JOIN `库存利润表` z  on b.`库存编号` = z.`库存编号`  " +
            //"                   -- 贴息金额  " +
            "                     LEFT JOIN lsh金融申请附属  ON B.初始报价单号 = lsh金融申请附属.初始报价单号         " +
            "                     LEFT JOIN (  " +
            //"                      -- 厂家随车SI，包括购置税  " +
            "                         SELECT SUM(随车成本) AS 厂家随车SI, 报价编号 FROM 报价随车表            " +
            "                         WHERE 随车类型 = '厂家'  GROUP BY 报价编号            " +
            "                     ) f ON f.报价编号 = b.报价编号          " +
            "                     LEFT JOIN (" +
            "                        SELECT A.STOCK_ID, SUM(IF(A.SI_BMBS_CODE='F', 0 , A.SI_AMOUNT)) 随车调整值 " +
            "                        FROM SI_BMBS_SETTING A, SI_SETTING B, LSH随车项目表 C " +
            "                        WHERE A.SET_TYPE = 1 AND A.SI_SETTING_ID = B.ID AND B.si_name_cn = C.随车 AND C.类型 = '厂家'" +
            "                        GROUP BY STOCK_ID " +
            "                     ) VEHICLE_BMBS_调整值 ON VEHICLE_BMBS_调整值.STOCK_ID = b.库存编号  " +
            "                     LEFT JOIN (" +
            "                        SELECT A.STOCK_ID, SUM(A.SI_AMOUNT)  贴息调整值 " +
            "                        FROM SI_BMBS_SETTING A " +
            "                        WHERE A.SET_TYPE = 1 AND A.SI_BMBS_CODE = 'P'" +
            "                        GROUP BY STOCK_ID " +
            "                     ) VEHICLE_贴息_调整值 ON VEHICLE_贴息_调整值.STOCK_ID = b.库存编号  " +
            "                     LEFT JOIN (  " +
            "                        SELECT PERMIT, STOCK_ID, SUM(IF(SI_BMBS_CODE='R', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
            "                               SUM(IF(SI_BMBS_CODE='W', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
            "                               SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT   " +
            "                        FROM SI_BMBS_SETTING    " +
            "                        WHERE (SI_BMBS_CODE='R' OR SI_BMBS_CODE='W' OR SI_BMBS_CODE='PT') AND SET_TYPE = 1  " +
            "                        GROUP BY PERMIT, STOCK_ID   " +
            "                    ) VEHICLE_SI_调整 ON VEHICLE_SI_调整.PERMIT =b.许可 AND VEHICLE_SI_调整.STOCK_ID = b.库存编号 " +
            "                    LEFT JOIN (   " +
            //"                 -- 预估零售SI,预估批售SI  " +
            "                        SELECT permit, carCode, BMBS_RETAIL_SI AS 预估零售SI, BMBS_WHOLESALE_SI AS 预估批售SI  " +
            "                        FROM snp_setting  WHERE y_m = '190001'   " +
            "                    ) snp_setting ON snp_setting.carCode = b.车型编号 and snp_setting.permit=b.许可  " +
            "                    ,LSH车型 d  " +
            "                    WHERE b.车型编号 = d.车型编号  " +
            "                    and ((date_format(b.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(b.开票时间,'%Y-%m-%d') >= '2000-01-01')      " +
            "                     OR ((date_format(b.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(b.退票时间,'%Y-%m-%d') >= '2000-01-01')))  " +
            "             )e  " +
            "             LEFT JOIN (  " +
            "                 SELECT A.PERMIT, D.来源, D.品牌, D.级别, SUM(IF(A.SI_BMBS_CODE='F', 0 , A.SI_AMOUNT)) 随车调整值 " +
            "                 FROM SI_BMBS_SETTING A, SI_SETTING B, LSH随车项目表 C, LSH车型 D  " +
            "                 WHERE SET_TYPE = 2 AND CAR_TYPE_ID = 车型编号 AND 级别 IS NOT NULL AND A.SI_SETTING_ID = B.ID AND B.si_name_cn = C.随车 AND C.类型 = '厂家' " +
            "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "                    AND '2000-01-01' <> '2020-01-01' " +
            "                 GROUP BY A.PERMIT, D.来源, D.品牌, D.级别   " +
            "            ) VEHICLE_MODEL_BMBS_调整 ON e.许可 = VEHICLE_MODEL_BMBS_调整.PERMIT AND e.来源 = VEHICLE_MODEL_BMBS_调整.来源 AND e.品牌 = VEHICLE_MODEL_BMBS_调整.品牌  AND e.级别 = VEHICLE_MODEL_BMBS_调整.级别" +
            "             LEFT JOIN (  " +
            "                 SELECT A.PERMIT, D.来源, D.品牌, D.级别, SUM(A.SI_AMOUNT)  贴息调整值 " +
            "                 FROM SI_BMBS_SETTING A, LSH车型 D  " +
            "                 WHERE SET_TYPE = 2 AND CAR_TYPE_ID = D.车型编号 AND D.级别 IS NOT NULL AND A.SI_BMBS_CODE = 'P' " +
            "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "                    AND '2000-01-01' <> '2020-01-01' " +
            "                 GROUP BY A.PERMIT, D.来源, D.品牌, D.级别  " +
            "            ) VEHICLE_MODEL_贴息_调整 ON e.许可 = VEHICLE_MODEL_贴息_调整.PERMIT AND e.来源 = VEHICLE_MODEL_贴息_调整.来源 AND e.品牌 = VEHICLE_MODEL_贴息_调整.品牌  AND e.级别 = VEHICLE_MODEL_贴息_调整.级别" +
            "             LEFT JOIN (  " +
            "                 SELECT A.PERMIT, D.来源, D.品牌, D.级别, " +
            "                        SUM(IF(SI_BMBS_CODE='R', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
            "                        SUM(IF(SI_BMBS_CODE='W', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
            "                        SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT   " +
            "                 FROM SI_BMBS_SETTING A, LSH车型 D  " +
            "                 WHERE (SI_BMBS_CODE='R' OR SI_BMBS_CODE='W' OR SI_BMBS_CODE='PT') AND SET_TYPE = 2 AND CAR_TYPE_ID = 车型编号 AND D.级别 IS NOT NULL " +
            "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "                    AND '2000-01-01' <> '2020-01-01' " +
            "                 GROUP BY A.PERMIT, D.来源, D.品牌, D.级别   " +
            "            ) VEHICLE_MODEL_SI_调整 ON e.许可 = VEHICLE_MODEL_SI_调整.PERMIT AND e.来源 = VEHICLE_MODEL_SI_调整.来源 AND e.品牌 = VEHICLE_MODEL_SI_调整.品牌  AND e.级别 = VEHICLE_MODEL_SI_调整.级别" +

            "            LEFT JOIN (  " +
            "                 SELECT A.PERMIT, D.来源, D.品牌, SUM(IF(A.SI_BMBS_CODE='F', 0 , A.SI_AMOUNT)) 随车调整值 " +
            "                 FROM SI_BMBS_SETTING A, SI_SETTING B, LSH随车项目表 C, LSH车型 D  " +
            "                 WHERE SET_TYPE = 2 AND CAR_TYPE_ID = 车型编号 AND 级别 IS NOT NULL AND A.SI_SETTING_ID = B.ID AND B.si_name_cn = C.随车 AND C.类型 = '厂家' " +
            "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "                    AND '2000-01-01' <> '2020-01-01' " +
            "                 GROUP BY A.PERMIT, D.来源, D.品牌   " +
            "            ) VEHICLE_BRAND_BMBS_调整 ON e.许可 = VEHICLE_BRAND_BMBS_调整.PERMIT AND e.来源 = VEHICLE_BRAND_BMBS_调整.来源 AND e.品牌 = VEHICLE_BRAND_BMBS_调整.品牌" +
            "             LEFT JOIN (  " +
            "                 SELECT A.PERMIT, D.来源, D.品牌, SUM(A.SI_AMOUNT)  贴息调整值 " +
            "                 FROM SI_BMBS_SETTING A, LSH车型 D  " +
            "                 WHERE SET_TYPE = 2 AND CAR_TYPE_ID = 车型编号 AND 级别 IS NOT NULL AND A.SI_BMBS_CODE = 'P' " +
            "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "                    AND '2000-01-01' <> '2020-01-01' " +
            "                 GROUP BY A.PERMIT, D.来源, D.品牌  " +
            "            ) VEHICLE_BRAND_贴息_调整 ON e.许可 = VEHICLE_BRAND_贴息_调整.PERMIT AND e.来源 = VEHICLE_BRAND_贴息_调整.来源 AND e.品牌 = VEHICLE_BRAND_贴息_调整.品牌" +
            "             LEFT JOIN (  " +
            "                 SELECT A.PERMIT, D.来源, D.品牌,  " +
            "                        SUM(IF(SI_BMBS_CODE='R', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
            "                        SUM(IF(SI_BMBS_CODE='W', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
            "                        SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT   " +
            "                 FROM SI_BMBS_SETTING A, LSH车型 D  " +
            "                 WHERE (SI_BMBS_CODE='R' OR SI_BMBS_CODE='W' OR SI_BMBS_CODE='PT') AND SET_TYPE = 2 AND CAR_TYPE_ID = 车型编号 AND 级别 IS NOT NULL " +
            "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "                    AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "                    AND '2000-01-01' <> '2020-01-01' " +
            "                 GROUP BY A.PERMIT, D.来源, D.品牌   " +
            "            ) VEHICLE_BRAND_SI_调整 ON e.许可 = VEHICLE_BRAND_SI_调整.PERMIT AND e.来源 = VEHICLE_BRAND_SI_调整.来源 AND e.品牌 = VEHICLE_BRAND_SI_调整.品牌" +
            "          GROUP BY e.许可,e.来源, e.品牌, e.级别     " +
            "  ) z  ON a.许可_BMBS = z.许可 where a.`是否测试` = '否'";

    // 采购批发数  批发日期为在此区间的车辆台数 (OK)  销售管理报表
    public static String purchaseStock = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode, 来源,  b.品牌, b.级别,b.实际采购" +
            "  FROM LSH经销商表 a LEFT JOIN (" +
            "  SELECT lsh库存表.`采购批发商` as 许可,  来源, " +
            "   `lsh车型`.`品牌`," +
            "   `lsh车型`.`级别`," +
            "   COUNT(1) 实际采购   " +
            "  FROM  `lsh库存表`   " +
            "  JOIN  `lsh车型`  ON  `lsh库存表`.`车型编号` = `lsh车型`.`车型编号` " +
            "  WHERE `lsh库存表`.`车架号` NOT LIKE 'IRIS%'  AND `lsh库存表`.is_delete<> '1'  " +
            "    AND date_format(`lsh库存表`.`批发日期`,'%Y-%m-%d')>='2000-01-01'" +
            "    AND date_format(`lsh库存表`.`批发日期`,'%Y-%m-%d')<='2020-01-01'" +
            "  group by 来源, `lsh车型`.品牌, `lsh车型`.级别,lsh库存表.`采购批发商`) b on a.许可_BMBS = b.许可 where a.`是否测试` = '否' ";

    //SC  人数    (OK  销售管理)
    public static String scNumber = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  b.smart人数, b.其他顾问人数  " +
            "FROM LSH经销商表 a LEFT JOIN (  " +
            "   select SUM(IF(`品牌` LIKE '%smart%',1,0)) AS smart人数,  " +
            "   SUM(IF(`品牌` LIKE '%MB%' OR `品牌` LIKE '%AMG%' OR `品牌` LIKE '%MM%',1,0)) as 其他顾问人数,许可  " +
            "                from `用户`  " +
            "                WHERE 角色='销售顾问'   " +
            "                AND (`职位`<>'实习' OR `职位` IS NULL)   " +
            "                AND (`删除状态` <>'已删除' or `删除状态` is NULL)  " +
            "   GROUP BY 许可) b on a.许可_BMBS = b.许可 where a.`是否测试` = '否' ";

    // 长库龄计算（OK）(CHECKED)
    public static String agingStockLt = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别,  " +
            "  if(b.150天库龄 is null,0 ,150天库龄) 150天,  " +
            "  if(b.120天库龄 is null,0 ,120天库龄) 120天,  " +
            "  if(b.90天_150天库龄 is null,0 ,90天_150天库龄) 90天_150天,  " +
            "  if(b.90天库龄 is null,0 ,90天库龄) 90天,  " +
            "  if(b.0_90天库龄 is null,0 ,0_90天库龄) 0_90天,  " +
            "  if(b.60天库龄 is null,0 ,60天库龄) 60天,  " +
            "  if(b.总库存 is null,0 ,总库存) 总库存  " +
            " FROM lsh经销商表 a  LEFT JOIN (   " +
            "     SELECT a.许可, 来源, a.品牌, a.级别, SUM(150天库龄) 150天库龄, SUM(120天库龄) 120天库龄, SUM(90天_150天库龄) 90天_150天库龄," +
            "            SUM(90天库龄) 90天库龄, SUM(0_90天库龄) 0_90天库龄, SUM(60天库龄) 60天库龄, SUM(总库存) 总库存 " +
            "     FROM ( " +
            "	      SELECT  b.所属经销商  as 许可, 来源, a.品牌, a.级别, 1 总库存, " +
            "                IF(DATEDIFF('2020-01-01', 批发日期) >= 150, 1 , 0) 150天库龄,  " +
            "                IF(DATEDIFF('2020-01-01', 批发日期) >= 120 && DATEDIFF('2020-01-01', 批发日期) < 150, 1 , 0) 120天库龄,  " +
            "                IF(DATEDIFF('2020-01-01', 批发日期) >= 90 && DATEDIFF('2020-01-01', 批发日期) < 150, 1 , 0) 90天_150天库龄,  " +
            "                IF(DATEDIFF('2020-01-01', 批发日期) >= 90 && DATEDIFF('2020-01-01', 批发日期) < 120, 1 , 0) 90天库龄, " +
            "                IF(DATEDIFF('2020-01-01', 批发日期) < 90 , 1 , 0) 0_90天库龄, " +
            "                IF(DATEDIFF('2020-01-01', 批发日期) >= 60 && DATEDIFF('2020-01-01', 批发日期) < 90, 1 , 0) 60天库龄 " +
            "	      FROM   LSH车型 a, LSH库存表 b  left join (select a.库存编号, b.开票时间, b.退票时间 " +
            "                                                  from 报价单表 a, 报价交车表 b, vw_max_quoted_code c , 报价订单表 " +
            "                                                  where a.报价编号 = c.报价编号 AND  a.交车 = b.编号 and a.订单编号 = d.订单编号 and d.`订单状态` = '开票C') c on b.库存编号 = c.库存编号 " +
            "         WHERE b.批发日期 <= '2020-01-01' and b.is_delete = 0 and b.车架号 not like 'IRIS%' and a.车型编号 = b.车型编号   " +
            "          and  (c.开票时间 IS NULL or (c.开票时间 > '2020-01-01 23:59:59' and c.退票时间 IS NULL) " +
            "               or (c.开票时间 > '2020-01-01 23:59:59' and c.退票时间 < c.开票时间))" +
            "	      AND (case when 'MB' = '' then true else a.品牌 = 'MB' end)   " +
            "	  ) a group by a.许可, 来源, a.品牌, a.级别   " +
            " ) b on a.许可_BMBS = b.许可 where a.`是否测试` = '否' ";

    // 其他服务费收入统计, 包括服务费单车营业额（OK）(CHECKED)
    public static String afterMarketServiceRevenue = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别,  " +
            " 上牌服务费, 其他服务费, (上牌服务费 + 其他服务费) 后市场服务费收入, b.新车销量   " +
            " FROM LSH经销商表 a  LEFT JOIN (" +
            "   SELECT a.许可, 来源, a.品牌, a.级别, " +
            "          SUM(TYPE * 上牌服务费) 上牌服务费, " +
            "          SUM(TYPE * 其他服务费) 其他服务费, " +
            "          SUM(TYPE * 新车销量) 新车销量    " +
            "   FROM (" +
            "	       SELECT  a.许可, 来源, c.品牌, c.级别, " +
            "              CASE " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND  " +
            "                      DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0 " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1 " +
            "                 WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1 " +
            "              END TYPE, " +
            "              b.上牌服务费 / 1.06 上牌服务费, " +
            "              b.其他服务费 / 1.06 其他服务费, " +
            "              1 新车销量 " +
            "          FROM vw_retailed_quotation a " +
            "          LEFT JOIN ( " +
            "		      SELECT 费用编号, SUM(IF(费用名称='上牌服务费', IFNULL(费用折后价,0),0)) 上牌服务费, SUM(IF(费用名称='其他服务费', IFNULL(费用折后价,0), 0)) 其他服务费     " +
            "		      FROM 报价费用详细表 a WHERE 费用名称='上牌服务费' OR 费用名称='其他服务费' GROUP BY a.`费用编号`     " +
            "	       ) b ON b.费用编号 = a.费用编号, LSH车型 c  " +
            "          WHERE a.车型编号 = c.车型编号 " +
            "            AND ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "	          OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01'))) " +
            "            AND (CASE WHEN 'MB' = '' THEN TRUE ELSE c.品牌 = 'MB' END)  " +
            "   ) a GROUP BY a.许可, 来源, 品牌, a.级别 " +
            " ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'";

    // 其他服务费收入(OK)(CHECKED)
    public static String otherServiceFeeMargin = "SELECT " +
            " allset.*, IFNULL(a.上牌服务费 , 0) AS 上牌服务费 , " +
            " IFNULL(a.其他服务费 , 0) AS 其他服务费 " +
            "FROM " +
            " kpi_allset allset " +
            "LEFT JOIN( " +
            " SELECT " +
            "  a.许可 AS permit , " +
            "  a.来源 , " +
            "  a.品牌 , " +
            "  a.`级别`, SUM(license_fee) 上牌服务费 , " +
            "  SUM(other_fee) 其他服务费 " +
            " FROM " +
            "  ( " +
            "   SELECT " +
            "    a.许可 , " +
            "    a.来源 , " +
            "    a.品牌 , " +
            "    a.`级别` , " +
            "    type * license_fee AS license_fee , " +
            "    type * other_fee AS other_fee " +
            "   FROM " +
            "    ( " +
            "     SELECT " +
            "      a.许可 , " +
            "      b.来源 , " +
            "      b.品牌 , " +
            "      b.`级别` , " +
            "      CASE " +
            "     WHEN DATE_FORMAT(a.开票时间 , '%Y-%m-%d') >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d') " +
            "     AND DATE_FORMAT(a.开票时间 , '%Y-%m-%d') <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d') " +
            "     AND DATE_FORMAT(a.退票时间 , '%Y-%m-%d') >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d') " +
            "     AND DATE_FORMAT(a.退票时间 , '%Y-%m-%d') <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d') " +
            "     AND a.退票时间 > a.开票时间 THEN " +
            "      0 " +
            "     WHEN DATE_FORMAT(a.开票时间 , '%Y-%m-%d') >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d') " +
            "     AND DATE_FORMAT(a.开票时间 , '%Y-%m-%d') <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d') THEN " +
            "      1 " +
            "     WHEN DATE_FORMAT(a.退票时间 , '%Y-%m-%d') >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d') " +
            "     AND DATE_FORMAT(a.退票时间 , '%Y-%m-%d') <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d') THEN " +
            "      - 1 " +
            "     END TYPE , " +
            "     CASE " +
            "    WHEN c.`费用名称` = '上牌服务费' THEN " +
            "     c.`费用折后价` " +
            "    ELSE " +
            "     0 " +
            "    END / 1.06 license_fee , " +
            "    CASE " +
            "   WHEN c.`费用名称` = '其他服务费' THEN " +
            "    c.`费用折后价` " +
            "   ELSE " +
            "    0 " +
            "   END / 1.06 other_fee " +
            "   FROM " +
            "    vw_retailed_quotation a " +
            "   JOIN LSH车型 b ON a.车型编号 = b.车型编号 " +
            "   LEFT JOIN `报价费用详细表` c ON a.`费用编号` = c.`费用编号` " +
            "   WHERE " +
            "    ( " +
            "     ( " +
            "      date_format(a.开票时间 , '%Y-%m-%d') <= '2020-01-01' " +
            "      AND date_format(a.开票时间 , '%Y-%m-%d') >= '2000-01-01' " +
            "     ) " +
            "     OR( " +
            "      ( " +
            "       date_format(a.退票时间 , '%Y-%m-%d') <= '2020-01-01' " +
            "       AND date_format(a.退票时间 , '%Y-%m-%d') >= '2000-01-01' " +
            "      ) " +
            "     ) " +
            "    ) " +
            "   AND( " +
            "    CASE " +
            "    WHEN 'MB' = '' THEN " +
            "     TRUE " +
            "    ELSE " +
            "     b.品牌 = 'MB' " +
            "    END " +
            "   ) " +
            "    ) a " +
            "  ) a " +
            " GROUP BY " +
            "  a.`许可` , " +
            "  a.`来源` , " +
            "  a.`品牌` , " +
            "  a.`级别` " +
            ") a ON a.permit = allset.permit " +
            "AND a.`来源` = allset.`来源` " +
            "AND a.`品牌` = allset.`品牌` " +
            "AND a.`级别` = allset.`级别` ";

    // 新保台数 + 新保手续费收入 + 新保渗透率(OK)(CHECKED)
    public static String newCarInsuranceCommission =
            "SELECT   " +
                    "  allset.*, IFNULL(SUM(   " +
                    "   CASE   " +
                    "   WHEN a.jq_order_date>= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "   AND a.jq_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "   AND a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "   AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "   AND a.ins_type = 0 THEN   " +
                    "    0   " +
                    "   WHEN a.jq_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "   AND a.jq_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "   AND a.is_ins_other != 2 THEN   " +
                    "    1   " +
                    "   WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "   AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "   AND a.ins_type = 0 THEN   " +
                    "    - 1   " +
                    "   END   " +
                    "  ),0) jq_count ,   " +
                    "  IFNULL(SUM(   " +
                    "   CASE   " +
                    "   WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "   AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "   AND a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "   AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "   AND a.ins_type = 1 THEN   " +
                    "    0   " +
                    "   WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "   AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "   AND a.is_ins_other != 2 THEN   " +
                    "    1   " +
                    "   WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "   AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "   AND a.ins_type = 1 THEN   " +
                    "    - 1   " +
                    "   END   " +
                    "  ),0)  新保台数 ,   " +
                    "  IFNULL(SUM(   " +
                    "   (   " +
                    "    CASE   " +
                    "    WHEN a.jq_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.jq_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.ins_type = 0 THEN   " +
                    "     0   " +
                    "    WHEN a.jq_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.jq_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.is_ins_other != 2 THEN   " +
                    "     1   " +
                    "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.ins_type = 0 THEN   " +
                    "     - 1   " +
                    "    END   " +
                    "   ) * a.jq_fee   " +
                    "  ),0)  +   " +
                    "  IFNULL(SUM(   " +
                    "   (   " +
                    "    CASE   " +
                    "    WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.ins_type = 1 THEN   " +
                    "     0   " +
                    "    WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.is_ins_other != 2 THEN   " +
                    "     1   " +
                    "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.ins_type = 1 THEN   " +
                    "     - 1   " +
                    "    END   " +
                    "   ) * a.sy_fee   " +
                    "  ),0) +    " +
                    "  IFNULL(SUM(a.ins_adjust),0) + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.insurance_poundage_adjust,0)/1.17 保险手续费 ,  " +
                    "  IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.insurance_poundage_adjust,0)/1.17 品牌保险手续费调整 ,  " +
                    "  SUM(   " +
                    "     " +
                    "    CASE   " +
                    "    WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.is_ins_other = 2 THEN   " +
                    "     0   " +
                    "    WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.is_ins_other = 2 THEN   " +
                    "     1   " +
                    "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.is_ins_other = 2 THEN   " +
                    "     - 1   " +
                    "    END   " +
                    "  ) 新车销量 , " +
                    " IFNULL(SUM(   " +
                    "   (   " +
                    "    CASE   " +
                    "    WHEN a.jq_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.jq_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.is_ins_other != 2 THEN   " +
                    "     a.首年交强险保费   " +
                    "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.ins_type = 0 THEN   " +
                    "     a.首年交强险保费*-1   " +
                    "    END   " +
                    "   )   " +
                    "  ),0) 首年交强险保费,   " +
                    " IFNULL(SUM(   " +
                    "   (   " +
                    "    CASE   " +
                    "    WHEN a.jq_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.jq_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.is_ins_other != 2 THEN   " +
                    "     a.交强险总保费   " +
                    "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.ins_type = 0 THEN   " +
                    "     a.交强险总保费*-1   " +
                    "    END   " +
                    "   )   " +
                    "  ),0) 交强险总保费,   " +
                    "  IFNULL(SUM(   " +
                    "   (   " +
                    "    CASE   " +
                    "    WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.is_ins_other != 2 THEN   " +
                    "     a.首年商业险保费 " +
                    "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.ins_type = 1 THEN   " +
                    "     a.首年商业险保费*- 1   " +
                    "    END   " +
                    "   )    " +
                    "  ),0) 首年商业险保费,    " +
                    "  IFNULL(SUM(   " +
                    "   (   " +
                    "    CASE   " +
                    "    WHEN a.sy_order_date  >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.sy_order_date  <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.is_ins_other != 2 THEN   " +
                    "     a.商业险总保费 " +
                    "    WHEN a.unsubscribe_date >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')   " +
                    "    AND a.unsubscribe_date <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')   " +
                    "    AND a.ins_type = 1 THEN   " +
                    "     a.商业险总保费*- 1   " +
                    "    END   " +
                    "   )    " +
                    "  ),0) 商业险总保费    " +
                    " FROM   " +
                    "  kpi_allset AS allset   " +
                    " LEFT JOIN kpi_ins_main AS a ON a.`permit` = allset.`许可_BMBS`   AND a.`来源` = allset.`来源` AND a.`品牌` = allset.`品牌` AND a.`级别` = allset.`级别` " +
                    " LEFT JOIN (" +
                    "      SELECT PERMIT, 来源, 品牌, 级别,   " +
                    "             SUM(insurance_poundage_adjust) insurance_poundage_adjust  " +
                    "      FROM INCOME_ADJUSTMENT, LSH车型   " +
                    "      WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
                    "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                    "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                    "      GROUP BY PERMIT, 来源, 品牌, 级别  " +
                    " ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON allset.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND allset.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND allset.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  allset.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
                    " LEFT JOIN (" +
                    "      SELECT PERMIT, 来源, 品牌,   " +
                    "             SUM(insurance_poundage_adjust) insurance_poundage_adjust " +
                    "      FROM INCOME_ADJUSTMENT, LSH车型   " +
                    "      WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
                    "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                    "            AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                    "      GROUP BY PERMIT, 来源, 品牌  " +
                    " ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON allset.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND allset.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND allset.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
                    " WHERE CASE WHEN 'MB' = '' THEN TRUE ELSE allset.品牌 = 'MB' END  " +
                    " GROUP BY   " +
                    "  allset.`许可_BMBS` ,   " +
                    "  allset.`来源` ,   " +
                    "  allset.`品牌` ,   " +
                    "  allset.`级别` ";

    //贷款总金额
    public static String loanTotal = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别,   " +
            "             b.贷款额 贷款总金额 " +
            "             FROM LSH经销商表 a  LEFT JOIN ( " +
            "               SELECT a.许可, a.品牌, a.级别, 来源, " +
            "                      SUM(TYPE * a.贷款额) 贷款额     " +
            "               FROM ( " +
            "                    SELECT  a.许可, 来源, d.品牌, d.级别, a.车型编号, a.开票时间,  " +
            "                          CASE  " +
            "                             WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND   " +
            "                                  DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0  " +
            "                             WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1  " +
            "                             WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1  " +
            "                          END TYPE,  " +
            "                          IFNULL(b.贷款额,0)  贷款额  " +
            "                      FROM vw_retailed_quotation a  " +
            "                      ,  报价金融表 b, LSH金融申请 c,  LSH车型 d  " +
            "                      WHERE a.`金融` = b.`金融编号` AND a.初始报价单号 = c.初始报价单号 and a.车型编号 = d.车型编号  " +
            "                        AND ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')     " +
            "                       OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01')))  " +
            "                        AND (CASE WHEN 'MB' = '' THEN TRUE ELSE d.品牌 = 'MB' END)   " +
            "                        AND C.状态 NOT IN ('04','09')   " +
            "                        AND B.金融机构 IS NOT NULL AND B.贷款额 > 0  " +
            "               ) a  " +
            "               GROUP BY a.许可,来源, 品牌, a.级别  " +
            "             ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否' ";

    //销售毛利GP3（OK）(CHECKED)
    private String salesGP3 = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
            " a.salesCode, a.来源,a.品牌, a.级别, " +
            " SUM(VEHICLE_GP_3.TYPE * (车辆销售价未税 - 消费税未税 - 采购价格未税 - (经销商赠送成本未税 + 延保赠送成本未税 + 精品赠送成本未税) + 厂家随车购置税 + 零售SI + 批发SI )) " +
            " - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.精品赠送成本调整差,0)/1.17  " +
            " - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.延保赠送成本调整差, 0)/1.17 " +
            " + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.R_SI_AMOUNT, 0) " +
            " + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.W_SI_AMOUNT, 0) " +
            " + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.PT_SI_AMOUNT, 0) AS GP3, " +
            " SUM(VEHICLE_GP_3.TYPE * (IF(LEFT(VEHICLE_GP_3.销售类型,1) IN ('B', 'D', 'I', 'P'), 0, VEHICLE_GP_3.车辆销售价未税 - VEHICLE_GP_3.消费税未税))) 新车外部销售收入, " +
            " 0 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.精品赠送成本调整差,0)/1.17  " +
            " - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.延保赠送成本调整差, 0)/1.17 " +
            " + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.R_SI_AMOUNT, 0) " +
            " + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.W_SI_AMOUNT, 0) " +
            " + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.PT_SI_AMOUNT, 0) AS 品牌调整值 " +
            " FROM kpi_allset a " +
            " LEFT JOIN ( " +
            "         SELECT a.许可, 来源, h.品牌, h.级别, i.销售类型, " +
            "             CASE " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND  " +
            "                      DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0 " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1 " +
            "                 WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1 " +
            "             END TYPE, " +
            "             IFNULL(a.折后价,0)/1.17 AS 车辆销售价未税, " +
            "             IFNULL(a.消费税,0) / 1.17 AS 消费税未税,      " +
            "		      case when a.所属经销商 = a.调入经销商 then ifnull(a.协商调车款项金额, 0)   " +
            "                  else IFNULL(a.采购价格含税, 0) end /1.17 AS 采购价格未税, " +
            "		      IFNULL(IFNULL(VEHICLE_SI_BMBS_调整.PT_SI_AMOUNT,IFNULL(BMBS随车.厂家随车购置税, 0)), 0) AS 厂家随车购置税,      " +
            "		      IFNULL(b.经销商赠送成本, 0) AS 经销商赠送成本未税,      " +
            "		      IFNULL(d.延保赠送成本, 0) / 1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.延保赠送成本调整差, 0) / 1.17 AS 延保赠送成本未税,    " +
            "		      IFNULL(e.精品赠送成本, 0) / 1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.精品赠送成本调整差, 0) / 1.17 AS 精品赠送成本未税,    " +
            "             IFNULL(IFNULL(IFNULL(VEHICLE_SI_BMBS_调整.R_SI_AMOUNT,z.零售SI), snp_setting.预估零售SI),0) AS 零售SI,  " +
            "             IFNULL(IF(a.所属经销商 = a.调入经销商, 0, IFNULL(IFNULL(VEHICLE_SI_BMBS_调整.W_SI_AMOUNT, Z.`批发SI`), snp_setting.预估批售SI)),0) AS `批发SI`  " +
            "	      FROM vw_retailed_quotation a  " +
            "         LEFT JOIN 库存利润表 z on a.库存编号 = z.库存编号 " +
            "         LEFT JOIN (  " +
            "             SELECT PERMIT, STOCK_ID, " +
            "                    SUM(IF(SI_NAME_CN = '零售SI', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
            "                    SUM(IF(SI_NAME_CN = '批售SI', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
            "                    SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT   " +
            "             FROM SI_BMBS_SETTING A , SI_SETTING B    " +
            "             WHERE A.SET_TYPE = 1 AND A.SI_SETTING_ID = B.ID  " +
            "             GROUP BY PERMIT, STOCK_ID   " +
            "         ) VEHICLE_SI_BMBS_调整 ON VEHICLE_SI_BMBS_调整.PERMIT =a.许可 AND VEHICLE_SI_BMBS_调整.STOCK_ID = a.库存编号 " +
            "	      LEFT JOIN (      " +
            "		      SELECT SUM(随车成本) AS 经销商赠送成本, 报价编号 FROM 报价随车表      " +
            "		      WHERE 随车类型 = '经销商' AND 随车部门 = '销售部' GROUP BY 报价编号      " +
            "	      ) b ON b.报价编号 = a.报价编号      " +
            "	      LEFT JOIN (      " +
            "		      SELECT SUM(随车成本) AS 厂家随车购置税, 报价编号 FROM 报价随车表      " +
            "		      WHERE 随车类型 = '厂家' AND 随车名称 = '购置税' GROUP BY 报价编号      " +
            "	      ) BMBS随车 ON BMBS随车.报价编号 = a.报价编号    " +
            "         LEFT JOIN ( " +
            "             SELECT permit, carCode, BMBS_RETAIL_SI AS 预估零售SI, BMBS_WHOLESALE_SI AS 预估批售SI " +
            "             FROM snp_setting  WHERE y_m = '190001' " +
            "         ) snp_setting ON snp_setting.carCode = a.车型编号 and snp_setting.permit=a.许可 " +
            "	      LEFT JOIN (      " +
            "		      SELECT 延保编号, IFNULL(延保原价,0) AS 延保赠送成本    " +
            "		      FROM 报价五星延保 WHERE 延保折后价 = 0   " +
            "	      ) d ON d.延保编号 = a.延保编号      " +
            "	      LEFT JOIN (      " +
            "		      SELECT  编号, sum(成本 * 数量) 精品赠送成本 " +
            "             FROM 报价精品详细表 WHERE   折后价 = 0 GROUP BY  编号" +
            "	      ) e ON e.编号 = a.精品      " +
            "         LEFT JOIN (" +
            "             SELECT STOCK_CODE, SUM(after_market_product_cost_adjust) 精品赠送成本调整差, SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust, 0)) 延保赠送成本调整差  " +
            "             FROM INCOME_ADJUSTMENT " +
            "             WHERE TYPE = 1 GROUP BY STOCK_CODE " +
            "         ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号 " +
            "	      , LSH车型 h, 报价订单表 i      " +
            "	      WHERE a.订单编号 = i.订单编号 and  ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "	          OR (date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01')) " +
            "            AND (CASE WHEN 'MB' = '' THEN TRUE ELSE h.品牌 = 'MB' END)  " +
            "            AND a.车型编号 = h.车型编号 " +
            "   ) VEHICLE_GP_3 ON a.许可_BMBS = VEHICLE_GP_3.许可 AND a.来源 = VEHICLE_GP_3.来源 AND a.品牌 = VEHICLE_GP_3.品牌 AND  a.级别 = VEHICLE_GP_3.级别  " +
            "   LEFT JOIN (" +
            "       SELECT PERMIT, 来源, 品牌, 级别, " +
            "              SUM(after_market_product_cost_adjust) 精品赠送成本调整差, " +
            "              SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust, 0)) 延保赠送成本调整差  " +
            "       FROM INCOME_ADJUSTMENT, LSH车型 " +
            "       WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
            "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "             AND '2000-01-01' <> '2020-01-01' " +
            "       GROUP BY PERMIT, 来源, 品牌, 级别 " +
            "   ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND  a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND" +
            "     a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
            "   LEFT JOIN (" +
            "       SELECT PERMIT, 来源, 品牌, " +
            "              SUM(after_market_product_cost_adjust) 精品赠送成本调整差, " +
            "              SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust, 0)) 延保赠送成本调整差  " +
            "       FROM INCOME_ADJUSTMENT, LSH车型 " +
            "       WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
            "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "             AND '2000-01-01' <> '2020-01-01' " +
            "       GROUP BY PERMIT, 来源, 品牌 " +
            "   ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND  a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌  " +
            "   LEFT JOIN (  " +
            "       SELECT PERMIT, 来源, 品牌, 级别, " +
            "              SUM(IF(SI_BMBS_CODE='R', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
            "              SUM(IF(SI_BMBS_CODE='W', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
            "              SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT   " +
            "       FROM SI_BMBS_SETTING, LSH车型    " +
            "       WHERE (SI_BMBS_CODE='R' OR SI_BMBS_CODE='W' OR SI_BMBS_CODE='PT') AND SET_TYPE = 2 AND (车型 IS NULL OR 车型 ='') " +
            "             AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "             AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "             AND '2000-01-01' <> '2020-01-01' " +
            "       GROUP BY PERMIT, 来源, 品牌, 级别   " +
            "   ) VEHICLE_MODEL_SI_BMBS_调整 ON a.许可_BMBS = VEHICLE_MODEL_SI_BMBS_调整.PERMIT AND a.来源 = VEHICLE_MODEL_SI_BMBS_调整.来源 AND a.品牌 = VEHICLE_MODEL_SI_BMBS_调整.品牌  AND a.级别 = VEHICLE_MODEL_SI_BMBS_调整.级别" +
            "   LEFT JOIN (  " +
            "       SELECT PERMIT, 来源, 品牌, " +
            "              SUM(IF(SI_BMBS_CODE='R', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
            "              SUM(IF(SI_BMBS_CODE='W', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
            "              SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT   " +
            "       FROM SI_BMBS_SETTING, LSH车型    " +
            "       WHERE (SI_BMBS_CODE='R' OR SI_BMBS_CODE='W' OR SI_BMBS_CODE='PT') AND SET_TYPE = 2 AND (级别 IS NULL OR 级别 ='') " +
            "             AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "             AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "             AND '2000-01-01' <> '2020-01-01' " +
            "       GROUP BY PERMIT, 来源, 品牌   " +
            "   ) VEHICLE_BRAND_SI_BMBS_调整 ON a.许可_BMBS = VEHICLE_BRAND_SI_BMBS_调整.PERMIT AND a.来源 = VEHICLE_BRAND_SI_BMBS_调整.来源 AND a.品牌 = VEHICLE_BRAND_SI_BMBS_调整.品牌  " +
            " WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
            " GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";

    // 销售毛利率TGP（OK）(CHECKED)
    private String salesTGP = "SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
            "   a.salesCode, a.来源,a.品牌, a.级别,   " +
            "   SUM(TYPE * (VEHICLE_GP.车辆销售价未税 - VEHICLE_GP.消费税未税 - VEHICLE_GP.采购价格未税)) GP1, " +

            "   SUM(VEHICLE_GP.TYPE * (VEHICLE_GP.车辆销售价未税 - VEHICLE_GP.消费税未税 - VEHICLE_GP.采购价格未税 - (经销商赠送成本未税 + 延保赠送成本未税 + 精品赠送成本未税))) - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17 - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17  GP1dot5, " +
            "   (0- IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17)  GP1dot5_品牌调整值, " +

            " SUM(VEHICLE_GP.TYPE * (VEHICLE_GP.车辆销售价未税 - VEHICLE_GP.消费税未税 -VEHICLE_GP.采购价格未税 - (经销商赠送成本未税 + 延保赠送成本未税 + 精品赠送成本未税  + 厂家随车赠送成本 +  贴息金额)))  " +
            " - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17 " +
            " - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17 " +
            " - IFNULL(VEHICLE_MODEL_SI_BMBS_调整.厂家随车赠送成本调整值,0) - IFNULL(VEHICLE_MODEL_SI_BMBS_调整.车辆贴息调整值,0) GP2, " +

            " -IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17 " +
            " -IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17 " +
            " - IFNULL(VEHICLE_BRAND_SI_BMBS_调整.厂家随车赠送成本调整值,0) + " +
            " + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.车辆贴息调整值,0) GP2_品牌调整值, " +

            " SUM(VEHICLE_GP.TYPE * (车辆销售价未税 - 消费税未税 - 采购价格未税 - (经销商赠送成本未税 + 延保赠送成本未税 + 精品赠送成本未税) + 厂家随车购置税 + 零售SI + 批发SI )) " +
            " - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17  " +
            " - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 " +
            " + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.R_SI_AMOUNT, 0) " +
            " + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.W_SI_AMOUNT, 0) " +
            " + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.PT_SI_AMOUNT, 0) AS GP3, " +
            " 0 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17  " +
            " - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 " +
            " + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.R_SI_AMOUNT, 0) " +
            " + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.W_SI_AMOUNT, 0) " +
            " + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.PT_SI_AMOUNT, 0) AS GP3_品牌调整值, " +

            "   SUM(TYPE * (IF(LEFT(VEHICLE_GP.销售类型,1) IN ('B', 'D', 'I', 'P'), 0, VEHICLE_GP.车辆销售价未税 - VEHICLE_GP.消费税未税))) 新车外部销售收入,    " +

            "   IFNULL(SUM(VEHICLE_GP.TYPE * (车辆销售价未税 - 消费税未税 - 采购价格未税 - (经销商赠送成本未税 + 延保赠送成本未税 + 精品赠送成本未税) +  厂家随车购置税 + 零售SI + 批发SI + 精品毛利未税 + 延保毛利未税 + 其他服务费未税 + 上牌服务费未税 + 金融服务费未税 + 金融手续费未税 + 车辆保险手续费调整未税)),0)  " +
            "   + IFNULL(报价保险.交强险手续费,0) / 1.17 " +
            "   + IFNULL(报价保险.商业险手续费,0) / 1.17 " +
            "   + IFNULL(ins_var_fee.VCI_fee,0) " +
            "   + IFNULL(ins_var_fee.TCI_fee,0) " +
            "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17  " +
            "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust,0)/1.17  " +
            "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_buy_adjust,0)/1.17  " +
            "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17  " +
            "   + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.R_SI_AMOUNT, 0) " +
            "   + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.W_SI_AMOUNT, 0) " +
            "   + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.PT_SI_AMOUNT, 0) " +
            "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 " +
            "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0)/1.17   AS tgp, " +
            "   IFNULL(SUM(VEHICLE_GP.TYPE * (车辆销售价未税 - 消费税未税  + 精品销售价格未税 + 延保销售价格未税 + 其他服务费未税 + 上牌服务费未税  + 金融服务费未税 + 金融手续费未税 + 车辆保险手续费调整未税)),0) " +
            "   + IFNULL(报价保险.交强险手续费,0) / 1.17 " +
            "   + IFNULL(报价保险.商业险手续费,0) / 1.17 " +
            "   + IFNULL(ins_var_fee.VCI_fee,0) " +
            "   + IFNULL(ins_var_fee.TCI_fee,0) " +
            "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 " +
            "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0) AS 销售毛收入,   " +
            "   0 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17  " +
            "   - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust,0)/1.17  " +
            "   - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_buy_adjust,0) /1.17 " +
            "   - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17  " +
            "   + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.R_SI_AMOUNT, 0) " +
            "   + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.W_SI_AMOUNT, 0) " +
            "   + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.PT_SI_AMOUNT, 0) " +
            "   + IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 " +
            "   + IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0)/1.17  AS tgp_品牌调整值, " +
            "   IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 " +
            "   + IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0) /1.17 AS 销售毛收入调整值 " +
            "   FROM kpi_allset a " +
            "   LEFT JOIN (   " +
            "       SELECT a.许可,  来源, g.品牌, g.级别, 销售类型,   " +
            "           CASE   " +
            "               WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND    " +
            "                    DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0   " +
            "               WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1   " +
            "               WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1   " +
            "           END TYPE,   " +
            "           IFNULL(a.折后价,0)/1.17 AS 车辆销售价未税,        " +
            "           IFNULL(a.消费税,0) / 1.17 AS 消费税未税,        " +
            "           case when a.所属经销商 = a.调入经销商 then ifnull(a.协商调车款项金额, 0)  else IFNULL(a.采购价格含税, 0) end /1.17 AS 采购价格未税,   " +
            "           IFNULL(GET_BMBS_AMOUNT(A.报价编号, A.库存编号),0) AS 厂家随车赠送成本, " +
            "           IFNULL(d.经销商赠送成本,0) AS 经销商赠送成本未税,        " +
            "           IFNULL(g.延保赠送成本,0)/1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 AS 延保赠送成本未税,        " +
            "           IFNULL(f.精品赠送成本,0)/1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.after_market_product_cost_adjust, 0)/1.17 AS 精品赠送成本未税,      " +
            "		    IFNULL(IFNULL(VEHICLE_SI_BMBS_调整.PT_SI_AMOUNT,IFNULL(d.厂家随车购置税, 0)), 0) AS 厂家随车购置税,      " +
            "           IFNULL(i.金融服务费,0) / 1.06 AS 金融服务费未税,        " +
            "           IFNULL(i.金融手续费,0) / 1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 AS 金融手续费未税,        " +
            "           IFNULL(VEHICLE_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0)/1.17 AS 车辆保险手续费调整未税,        " +
            "           IFNULL(k.置换评估费,0) / 1.17 AS 置换评估费未税,        " +
            "           IFNULL(g.延保毛利, 0) / 1.17 - IFNULL(VEHICLE_INCOME_ADJUSTMENT.five_stars_extend_cost_buy_adjust, 0)/1.17AS 延保毛利未税,        " +
            "           IFNULL(g.延保销售价格, 0) / 1.17 AS 延保销售价格未税,        " +
            "           IFNULL(f.精品毛利, 0) / 1.17 - IFNULL(VEHICLE_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust,0)/1.17 AS 精品毛利未税,      " +
            "           IFNULL(f.精品销售价格, 0) / 1.17 AS 精品销售价格未税,   " +
            "		    IFNULL(VEHICLE_SI_BMBS_调整.车辆贴息调整值, IFNULL(lsh金融申请附属.贴息金额, 0)) AS 贴息金额,    " +
            "           IFNULL(IFNULL(IFNULL(VEHICLE_SI_BMBS_调整.R_SI_AMOUNT,z.零售SI), snp_setting.预估零售SI),0) AS 零售SI,  " +
            "           IFNULL(IF(a.所属经销商 = a.调入经销商, 0, IFNULL(IFNULL(VEHICLE_SI_BMBS_调整.W_SI_AMOUNT, Z.`批发SI`), snp_setting.预估批售SI)),0) AS `批发SI`,      " +
            "           IFNULL(h.其他服务费,0) / 1.06 AS 其他服务费未税,        " +
            "           IFNULL(h.上牌服务费, 0) / 1.06 AS 上牌服务费未税      " +
            "       FROM vw_retailed_quotation a    " +
            "       LEFT JOIN 库存利润表 z on a.库存编号 = z.库存编号 " +

            "       LEFT JOIN (            " +
            "         SELECT 报价编号," +
            "                SUM(IF(随车类型 = '经销商' AND 随车部门 = '销售部', 随车成本, 0)) AS 经销商赠送成本, " +
            "                SUM(IF(随车类型 = '厂家' AND 随车名称 <> '购置税', 随车成本, 0)) AS 厂家随车SI成本, " +
            "                SUM(IF(随车类型 = '厂家' AND 随车名称 = '购置税', 随车成本, 0)) AS 厂家随车购置税 " +
            "         FROM 报价随车表 GROUP BY 报价编号  " +
            "       ) d ON d.报价编号 = a.报价编号 " +

            "       LEFT JOIN (        " +
            "         SELECT  编号, " +
            "                 SUM(IF(折后价 = 0, 成本 * 数量, 0)) 精品赠送成本,   " +
            "                 SUM(IF(折后价 > 0, `折后价` * 数量, 0)) 精品销售价格,   " +
            "                 SUM(IF(折后价 > 0, `折后价` * 数量 - 成本 * 数量, 0)) 精品毛利 " +
            "           FROM 报价精品详细表 GROUP BY  编号  " +
            "       ) f ON f.编号 = a.精品        " +

            "       LEFT JOIN (          " +
            "         SELECT 延保编号, " +
            "                IF(延保折后价 = 0, IFNULL(延保原价,0),0) AS 延保赠送成本,      " +
            "                IF(延保折后价 > 0, IFNULL(延保折后价,0),0) 延保销售价格,      " +
            "                IF(延保折后价 > 0, IFNULL(延保折后价,0) - IFNULL(延保原价,0),0) 延保毛利 " +
            "         FROM 报价五星延保      " +
            "       ) g ON g.延保编号 = a.延保编号          " +

            "       LEFT JOIN (        " +
            "         SELECT 费用编号, " +
            "         SUM(IF(费用名称 = '其他服务费', 费用折后价,0)) AS 其他服务费,        " +
            "         SUM(IF(费用名称 = '上牌服务费', 费用折后价,0)) AS 上牌服务费        " +
            "         FROM 报价费用详细表  " +
            "         GROUP BY 费用编号        " +
            "       ) h ON h.费用编号 = a.费用编号      " +

            "       LEFT JOIN 报价金融表 i ON i.金融编号 = a.金融        " +
            "       LEFT JOIN lsh金融申请附属  ON a.初始报价单号 = lsh金融申请附属.初始报价单号         " +
            "       LEFT JOIN 报价置换表 k ON k.置换编号 = a.置换编号  " +
            "       LEFT JOIN (  " +
            "           SELECT PERMIT, STOCK_CODE,   " +
            "                  SUM(finance_poundage_adjust) finance_poundage_adjust, " +
            "                  SUM(insurance_poundage_adjust) insurance_poundage_adjust,   " +
            "                  SUM(after_market_product_cost_adjust) after_market_product_cost_adjust, " +
            "                  SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust, " +
            "                  SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust, 0)) five_stars_extend_cost_adjust,   " +
            "                  SUM(IF(IS_GIVE=0, five_stars_extend_cost_adjust, 0)) five_stars_extend_cost_buy_adjust   " +
            "           FROM INCOME_ADJUSTMENT   " +
            "           WHERE TYPE = 1  " +
            "           GROUP BY PERMIT, STOCK_CODE " +
            "        ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.PERMIT =a.许可 AND VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号  " +
            "        LEFT JOIN (  " +
            "           SELECT PERMIT, STOCK_ID, " +
            "                  SUM(IF(SI_NAME_CN = '零售SI', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
            "                  SUM(IF(SI_NAME_CN = '批售SI', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
            "                  SUM(IF(SI_NAME_CN = '购置税', SI_AMOUNT, 0)) PT_SI_AMOUNT,   " +
            "                  SUM(IF(SI_NAME_CN = '贴息', SI_AMOUNT, 0)) 车辆贴息调整值 " +
            "           FROM SI_BMBS_SETTING A , SI_SETTING B    " +
            "           WHERE A.SET_TYPE = 1 AND A.SI_SETTING_ID = B.ID  " +
            "           GROUP BY PERMIT, STOCK_ID   " +
            "        ) VEHICLE_SI_BMBS_调整 ON VEHICLE_SI_BMBS_调整.PERMIT =a.许可 AND VEHICLE_SI_BMBS_调整.STOCK_ID = a.库存编号 " +
            "       LEFT JOIN (   " +
            "           SELECT permit, carCode, BMBS_RETAIL_SI AS 预估零售SI, BMBS_WHOLESALE_SI AS 预估批售SI  " +
            "           FROM snp_setting  WHERE y_m = '190001'   " +
            "       ) snp_setting ON snp_setting.carCode = a.车型编号 and snp_setting.permit=a.许可   " +
            "       ,LSH车型 g , 报价订单表 h     " +
            "       WHERE a.订单编号 = h.订单编号 AND ((a.开票时间 <= '2020-01-01 23:59:59'  AND a.开票时间 >= '2000-01-01 00:00:01')      " +
            "         OR (a.退票时间 <= '2020-01-01 23:59:59'  AND a.退票时间 >= '2000-01-01 00:00:01'))   " +
            "          AND (CASE WHEN 'MB' = '' THEN TRUE ELSE g.品牌 = 'MB' END)    " +
            "          AND a.车型编号 = g.车型编号   " +
            "   ) VEHICLE_GP ON a.许可_BMBS = VEHICLE_GP.许可 AND a.来源 = VEHICLE_GP.来源 AND a.品牌 = VEHICLE_GP.品牌 AND  a.级别 = VEHICLE_GP.级别 " +
            "   LEFT JOIN (" +
            "        SELECT b.许可, d.来源, d.品牌, d.级别 ,IF(a.类型='0',SUM(a.poundage),0) TCI_fee, IF(a.类型='1',SUM(a.poundage),0) VCI_fee " +
            "        FROM lsh保险申请表附属_详细 a, 报价单表 b, vw_max_quotedcode_all c, LSH车型 d  " +
            "        where 批单日期 >= '2000-01-01' and 批单日期 <= '2020-01-01'    " +
            "        and a.初始报价单号 = b.初始报价单号 and b.车型编号 = d.车型编号 and b.报价编号 = c.报价编号 " +
            "        GROUP BY b.许可, d.来源, d.品牌, d.级别  " +
            "   ) ins_var_fee ON a.许可_BMBS = ins_var_fee.许可 AND a.来源 = ins_var_fee.来源 AND a.品牌 = ins_var_fee.品牌 AND  a.级别 = ins_var_fee.级别 " +
            "   LEFT JOIN (" +
            "       SELECT b.许可, e.来源, e.品牌, e.级别, sum(d.商业险手续费) 商业险手续费, sum(d.交强险手续费) 交强险手续费 " +
            "       FROM LSH保险申请附属 a, 报价单表 B, vw_max_quotedcode_all c, 报价保险表 d, `lsh车型` e " +
            "       WHERE A.初始报价单号 = b.初始报价单号 and b.报价编号 = c.报价编号 AND b.保险编号 = d.保险编号 and b.车型编号 = e.车型编号 " +
            "        and a.商业险出单日期 >= '2000-01-01'  " +
            "        and a.商业险出单日期 <= '2020-01-01' " +
            "       group by b.许可, e.来源, e.品牌, e.级别   " +
            "   ) 报价保险 ON a.许可_BMBS = 报价保险.许可 and a.来源 = 报价保险.来源 and a.品牌 = 报价保险.品牌 and a.级别 = 报价保险.级别" +
            "   LEFT JOIN (" +
            "        SELECT PERMIT, 来源, 品牌, 级别,   " +
            "               SUM(finance_poundage_adjust) finance_poundage_adjust, " +
            "               SUM(insurance_poundage_adjust) insurance_poundage_adjust,   " +
            "               SUM(after_market_product_cost_adjust) after_market_product_cost_adjust, " +
            "               SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust, " +
            "               SUM(IF(IS_GIVE=1, 0, five_stars_extend_cost_adjust)) five_stars_extend_cost_buy_adjust, " +
            "               SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust,0)) five_stars_extend_cost_adjust   " +
            "        FROM INCOME_ADJUSTMENT, LSH车型   " +
            "        WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
            "          AND ADJUST_DATE >= '2000-01-01'  " +
            "          AND ADJUST_DATE <= '2020-01-01' " +
            "          AND '2000-01-01' <> '2020-01-01' " +
            "        GROUP BY PERMIT, 来源, 品牌, 级别  " +
            "   ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
            "   LEFT JOIN (" +
            "        SELECT PERMIT, 来源, 品牌,   " +
            "               SUM(finance_poundage_adjust) finance_poundage_adjust, " +
            "               SUM(insurance_poundage_adjust) insurance_poundage_adjust,   " +
            "               SUM(after_market_product_cost_adjust) after_market_product_cost_adjust, " +
            "               SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust, " +
            "               SUM(IF(IS_GIVE=1, 0, five_stars_extend_cost_adjust)) five_stars_extend_cost_buy_adjust, " +
            "               SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust,0)) five_stars_extend_cost_adjust   " +
            "        FROM INCOME_ADJUSTMENT, LSH车型   " +
            "        WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
            "          AND ADJUST_DATE >= '2000-01-01'  " +
            "          AND ADJUST_DATE <= '2020-01-01' " +
            "          AND '2000-01-01' <> '2020-01-01' " +
            "        GROUP BY PERMIT, 来源, 品牌  " +
            "   ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
            "   LEFT JOIN (  " +
            "        SELECT PERMIT, 来源, 品牌, 级别, " +
            "               SUM(IF(SI_BMBS_CODE='R', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
            "               SUM(IF(SI_BMBS_CODE='W', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
            "               SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT,   " +
            "               0 厂家随车赠送成本调整值, " +
            "               0 车辆贴息调整值   " +
            "        FROM SI_BMBS_SETTING, LSH车型    " +
            "        WHERE (SI_BMBS_CODE='R' OR SI_BMBS_CODE='W' OR SI_BMBS_CODE='PT') AND SET_TYPE = 2 AND (车型 IS NULL OR 车型 ='') " +
            "            AND VALID_START_DATE >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "            AND VALID_START_DATE <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "            AND '2000-01-01' <> '2020-01-01' " +
            "        GROUP BY PERMIT, 来源, 品牌, 级别   " +
            "   ) VEHICLE_MODEL_SI_BMBS_调整 ON a.许可_BMBS = VEHICLE_MODEL_SI_BMBS_调整.PERMIT AND a.来源 = VEHICLE_MODEL_SI_BMBS_调整.来源 AND a.品牌 = VEHICLE_MODEL_SI_BMBS_调整.品牌  AND a.级别 = VEHICLE_MODEL_SI_BMBS_调整.级别" +
            "   LEFT JOIN (  " +
            "        SELECT PERMIT, 来源, 品牌, " +
            "              SUM(IF(SI_BMBS_CODE='R', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
            "              SUM(IF(SI_BMBS_CODE='W', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
            "              SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT,   " +
            "               0 厂家随车赠送成本调整值, " +
            "               0 车辆贴息调整值   " +
            "        FROM SI_BMBS_SETTING, LSH车型    " +
            "        WHERE (SI_BMBS_CODE='R' OR SI_BMBS_CODE='W' OR SI_BMBS_CODE='PT') AND SET_TYPE = 2 AND (级别 IS NULL OR 级别 ='') " +
            "           AND VALID_START_DATE >= DATE_FORMAT('2000-01-01','%Y-%m') " +
            "           AND VALID_START_DATE <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "           AND '2000-01-01' <> '2020-01-01' " +
            "        GROUP BY PERMIT, 来源, 品牌   " +
            "   ) VEHICLE_BRAND_SI_BMBS_调整 ON a.许可_BMBS = VEHICLE_BRAND_SI_BMBS_调整.PERMIT AND a.来源 = VEHICLE_BRAND_SI_BMBS_调整.来源 AND a.品牌 = VEHICLE_BRAND_SI_BMBS_调整.品牌  " +
            "  WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
            "  GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";

    // 本地上牌率(KO)
    private String licRate = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,   来源, b.品牌, b.级别,  " +
            "                  sum(case b.上牌类型  when '本地摇号/拍牌' then 1 when '本地有号牌' then 1 else 0 end) 本地上牌的新车台数, count(b.上牌类型) 交车数    " +
            "            from lsh经销商表 a left outer join ( select b.*, c.上牌类型,  来源, d.品牌, d.级别       " +
            "                                                 from vw_retailed_quotation b , 报价客户表 c, LSH车型 d     " +
            "                                                 where b.`客户详细` = c.编号     " +
            "												  and b.`车型编号` = d.车型编号 and  (case when 'MB' = '' then true else d.品牌 = 'MB' end)    " +
            "                                                 and b.车辆零售状态 = '已卖'  " +
            "                                                 and b.退车日期 is null  " +
            "                                                 and date_format(b.交车日期,'%Y-%m-%d') >= '2000-01-01' and date_format(b.交车日期,'%Y-%m-%d') <= '2020-01-01'     " +
            "                                              ) b on a.许可_BMBS = b.许可 where a.`是否测试` = '否'    " +
            "            group by a.`许可_BMBS`,  b.来源, b.品牌,b.级别    ";

    // 销售订单完成率(OK)
    private String salesOrderAchievement = "SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别,   " +
            " sum(b.订单数) 新增订单数, sum(b.lsh_销售目标) as lsh_销售目标    " +
            "from lsh经销商表 a left join ( " +
            " select sum(CASE " +
            "            WHEN date_format(b.订单审批通过日期,'%Y-%m-%d') >= '2000-01-01'  and date_format(b.订单审批通过日期,'%Y-%m-%d')  <= '2020-01-01'   " +
            "                 AND  date_format(b.订单失销日期,'%Y-%m-%d') >= '2000-01-01' and date_format(b.订单失销日期,'%Y-%m-%d')  <= '2020-01-01'   " +
            "            THEN 0 " +
            "            WHEN date_format(b.订单审批通过日期,'%Y-%m-%d') >= '2000-01-01' and date_format(b.订单审批通过日期,'%Y-%m-%d') <='2020-01-01' THEN 1  " +
            "            WHEN date_format(b.订单失销日期,'%Y-%m-%d') >= '2000-01-01'  and  date_format(b.订单失销日期,'%Y-%m-%d') <= '2020-01-01' THEN -1  " +
            "            END) as 订单数, 0  lsh_销售目标, " +
            "            b.许可 , 来源, d.品牌,d.级别 " +
            "  from  VW_ORDER_QUOTATION  b  " +
            "  left join LSH车型 d on b.`车型编号` = d.车型编号    " +
            "  where ((date_format(b.订单审批通过日期,'%Y-%m-%d') <= '2020-01-01'  AND date_format(b.订单审批通过日期,'%Y-%m-%d') >= '2000-01-01')  " +
            "     OR ((date_format(b.订单失销日期,'%Y-%m-%d') <= '2020-01-01'  AND date_format(b.订单失销日期,'%Y-%m-%d') >= '2000-01-01')))       " +
            "    and (case when 'MB' = '' then true else d.品牌 = 'MB' end)  " +
            "    group by b.许可 , 来源, d.品牌,d.级别       " +
            "  union all " +
            "  SELECT 0 订单数, sum(lsh_ta) as lsh_销售目标, permit 许可 , 来源, 品牌,级别  " +
            "  FROM snp_setting a     " +
            "  join LSH车型 b on a.carCode = b.车型编号 and y_m <> '190001'     " +
            "  WHERE y_m >= concat(substr('2000-01-01', 1, 4), substr('2000-01-01', 6, 2))     " +
            "	 AND y_m <= concat(substr('2020-01-01', 1, 4), substr('2020-01-01', 6, 2))     " +
            "	 and (case when 'MB' = '' then true else b.品牌 = 'MB' end)     " +
            "	 group by a.permit, 来源, 品牌,级别   " +
            "  ) b on a.`许可_BMBS` = b.许可  where a.`是否测试` = '否'  " +
            "  group by a.`许可_BMBS`,  b.来源, b.品牌,b.级别    ";

    // 订单池比例(OK)
    private String totalUnfulfilledOrder = "SELECT allset.*,IFNULL(a.订单数,0) AS 订单数,IFNULL(b.lsh_销售目标,0) as lsh_销售目标 FROM   " +
            " kpi_allset allset  " +
            "LEFT JOIN   " +
            "(  " +
            "  SELECT  a.许可, b.来源, b.品牌, b.级别,count(1) 订单数  " +
            "  FROM VW_ORDER_QUOTATION a  " +
            "  JOIN LSH车型 b ON a.车型编号 = b.车型编号   " +
            "  WHERE (CASE WHEN 'MB' = '' THEN TRUE ELSE b.品牌 = 'MB' END)  " +
            "  AND A.订单审批通过日期 <= '2020-01-01' " +
            "  GROUP BY a.许可,b.来源,b.品牌,b.级别  " +
            ") a ON a.`许可`=allset.许可_BMBS AND a.`来源`=allset.`来源` AND a.`品牌`=allset.`品牌` AND a.`级别`=allset.`级别`  " +
            "LEFT JOIN  " +
            "(  " +
            "   SELECT permit,b.`来源`,b.`品牌`,b.`级别`, sum(IFNULL(lsh_ta,0)) as lsh_销售目标  " +
            "   FROM snp_setting a     " +
            "   JOIN LSH车型 b on a.carCode = b.车型编号 and y_m <> '190001'     " +
            "   WHERE y_m >= concat(substr('2000-01-01', 1, 4), substr('2000-01-01', 6, 2))     " +
            "     AND y_m <= concat(substr('2020-01-01', 1, 4), substr('2020-01-01', 6, 2))     " +
            "     AND (case when 'MB' = '' then true else b.品牌 = 'MB' end)    " +
            "     GROUP BY a.permit,b.`来源`,b.`品牌`,b.`级别`    " +
            ") b ON b.permit=allset.许可_BMBS AND b.`来源`=allset.`来源` AND b.`品牌`=allset.`品牌` AND b.`级别`=allset.`级别`  ";

    // 当月预计可开票订单池比例(OK)
    private String invoicableUnfulfilledOrderWithExistStock =
            "SELECT allset.*,IFNULL(a.可开票订单数,0) AS 可开票订单数,IFNULL(b.lsh_销售目标,0) as lsh_销售目标 FROM   " +
                    " kpi_allset allset  " +
                    "LEFT JOIN   " +
                    "(  " +
                    "  SELECT  a.许可, b.来源, b.品牌, b.级别,   " +
                    "    count(1) 可开票订单数  " +
                    "  FROM VW_ORDER_QUOTATION a  " +
                    "  join LSH车型 b ON a.车型编号 = b.车型编号   " +
                    "  WHERE  (date_format(a.预计开票日期,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.预计开票日期,'%Y-%m-%d') >= '2000-01-01')  " +
                    "  AND A.订单审批通过日期 <= '2020-01-01' " +
                    "  AND (CASE WHEN 'MB' = '' THEN TRUE ELSE b.品牌 = 'MB' END)  " +
                    "  GROUP BY a.许可,b.来源,b.品牌,b.级别  " +
                    ") a ON a.`许可`=allset.许可_BMBS AND a.`来源`=allset.`来源` AND a.`品牌`=allset.`品牌` AND a.`级别`=allset.`级别`  " +
                    "LEFT JOIN  " +
                    "(  " +
                    "              SELECT permit,b.`来源`,b.`品牌`,b.`级别`, sum(IFNULL(lsh_ta,0)) as lsh_销售目标  " +
                    "              FROM snp_setting a     " +
                    "              join LSH车型 b on a.carCode = b.车型编号 and y_m <> '190001'     " +
                    "              WHERE y_m >= concat(substr('2000-01-01', 1, 4), substr('2000-01-01', 6, 2))     " +
                    "              AND y_m <= concat(substr('2020-01-01', 1, 4), substr('2020-01-01', 6, 2))     " +
                    "              and (case when 'MB' = '' then true else b.品牌 = 'MB' end)    " +
                    "               group by a.permit,b.`来源`,b.`品牌`,b.`级别`    " +
                    ") b ON b.permit=allset.许可_BMBS AND b.`来源`=allset.`来源` AND b.`品牌`=allset.`品牌` AND b.`级别`=allset.`级别`";

    // 销售目标与新增展厅客流比(OK)
    private String retailTAvsS_RTrafficActual =
            "SELECT allset.*, IFNULL(a.进店线索数,0) as 进店线索数,IFNULL(b.lsh_销售目标,0) as lsh_销售目标 FROM   " +
                    " kpi_allset allset  " +
                    "LEFT JOIN  " +
                    "(  " +
                    "             SELECT 许可, 来源, 品牌, 级别, COUNT(顾客编号) 进店线索数   " +
                    "                   FROM (   " +
                    "                      SELECT a.许可, c.来源, c.品牌, c.级别,  顾客编号      " +
                    "                      FROM 顾客进店记录表 a, " +
                    "                      LSH车型 c       " +
                    "                      WHERE a.`进店日期` >= '2000-01-01' and a.`进店日期` <= '2020-01-01'      " +
                    "                         AND (a.到店目的 IN ('看车比价','订单') OR (a.到店目的 IN ('办手续','交车') AND a.是否订单='是')) " +
                    "                         AND left(a.到店时间,2) not in ('00','01','02','03')   " +
                    "                         AND a.意向车型A = c.车型编号   " +
                    "                         AND (CASE WHEN 'MB' = '' THEN TRUE ELSE C.品牌 = 'MB' END)    " +
                    "                       GROUP BY  a.顾客编号   " +
                    "                   ) a GROUP BY 许可, 来源, 品牌, 级别    " +
                    ") a ON a.许可 = allset.许可_BMBS AND a.`来源`=allset.`来源` AND a.`品牌`=allset.`品牌` AND a.`级别`=allset.`级别`  " +
                    "LEFT JOIN  " +
                    "(  " +
                    "  SELECT permit,b.`来源`,b.`品牌`,b.`级别`, sum(IFNULL(lsh_ta,0)) as lsh_销售目标  " +
                    "  FROM snp_setting a     " +
                    "  join LSH车型 b on a.carCode = b.车型编号 and y_m <> '190001'     " +
                    "  WHERE y_m >= concat(substr('2000-01-01', 1, 4), substr('2000-01-01', 6, 2))     " +
                    "  AND y_m <= concat(substr('2020-01-01', 1, 4), substr('2020-01-01', 6, 2))     " +
                    "  and (case when 'MB' = '' then true else b.品牌 = 'MB' end)    " +
                    "       group by a.permit,b.`来源`,b.`品牌`,b.`级别`  " +
                    ") b ON b.permit=allset.许可_BMBS AND b.`来源`=allset.`来源` AND b.`品牌`=allset.`品牌` AND b.`级别`=allset.`级别`";

    // 新增线索进店转化率(OK,((MGT统计开始日<=进店日期<=MGT统计结束日)的首次进店且未战败的线索数)/新增线索数)
    private String newClueEnteringStoreConversionRate =
            "SELECT allset.*,IFNULL(c.未战败线索数,0) AS 首次进店且未战败的线索数,IFNULL(a.散客线索数,0)+IFNULL(b.导入线索数,0) AS 新增线索数  " +
                    " FROM   " +
                    " kpi_allset allset  " +
                    "LEFT JOIN  " +
                    "(  " +
                    "  SELECT  " +
                    "    a.`许可`,b.`来源`,b.`品牌`,b.`级别`,  " +
                    "    COUNT(DISTINCT(a.`顾客编号`)) AS 散客线索数  " +
                    "  FROM  " +
                    "    `顾客进店记录表` a,  " +
                    "    lsh车型 b  " +
                    "  WHERE  " +
                    "    a.`是否首次到店` = '是'  " +
                    "  AND a.`顾客来源` = '散客'  " +
                    "  AND a.`进店日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                    "  AND a.`进店日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                    "  AND a.`意向车型A` = b.`车型编号`  " +
                    "  GROUP BY a.`许可`,b.`来源`,b.`品牌`,b.`级别`  " +
                    ") a ON a.`许可`=allset.许可_BMBS AND a.`来源`=allset.`来源` AND a.`品牌`=allset.`品牌` AND a.`级别`=allset.`级别`  " +
                    "LEFT JOIN  " +
                    "(  " +
                    "  SELECT  " +
                    "    a.`许可`,b.`来源`,b.`品牌`,b.`级别`,  " +
                    "    COUNT(DISTINCT(CONCAT(a.`许可`,a.`电话号码`))) AS 导入线索数  " +
                    "  FROM  " +
                    "    lsh导入名单 a,  " +
                    "    lsh车型 b  " +
                    "  WHERE  " +
                    "    a.`是否线索` = '是'  " +
                    "  AND a.`是否有效线索` = '是'  " +
                    "  AND a.`导入日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                    "  AND a.`导入日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                    "  AND a.`车型编号`=b.`车型编号`  " +
                    "  GROUP BY a.`许可`,b.`来源`,b.`品牌`,b.`级别`  " +
                    ") b ON b.`许可`=allset.许可_BMBS AND b.`来源`=allset.`来源` AND b.`品牌`=allset.`品牌` AND b.`级别`=allset.`级别`  " +
                    "LEFT JOIN  " +
                    "(  " +
                    "  SELECT  " +
                    "    a.`许可`,b.`来源`,b.`品牌`,b.`级别`,  " +
                    "    COUNT(DISTINCT(a.`顾客编号`)) AS 未战败线索数  " +
                    "  FROM  " +
                    "    `顾客进店记录表` a,  " +
                    "    `lsh车型` b,  " +
                    "    `顾客记录汇总` c  " +
                    "  WHERE  " +
                    "    a.`是否首次到店` = '是'  " +
                    "  AND a.`进店日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                    "  AND a.`进店日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                    "  AND a.`意向车型A` = b.`车型编号`  " +
                    "  AND a.`顾客编号` = c.`顾客编号`  " +
                    "  AND NOT EXISTS   " +
                    "  (  " +
                    "    SELECT  " +
                    "      *  " +
                    "    FROM  " +
                    "      `跟进记录表` follow  " +
                    "    WHERE  " +
                    "      follow.`许可` = c.`许可`  " +
                    "    AND follow.`电话号码` = c.`电话号码`  " +
                    "    AND follow.`跟进日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                    "    AND follow.`跟进日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                    "  )  " +
                    "  GROUP BY a.`许可`,b.`来源`,b.`品牌`,b.`级别`  " +
                    ") c ON c.`许可`=allset.许可_BMBS AND c.`来源`=allset.`来源` AND c.`品牌`=allset.`品牌` AND c.`级别`=allset.`级别`";

    // 未关闭线索的再次进店转化率(OK,同新增线索进店转化率)
    private String unclosedClueReEnteringStoreConversionRate =
            "SELECT allset.*,IFNULL(c.未战败线索数,0) AS 再次进店且未战败的线索数,IFNULL(a.散客线索数,0)+IFNULL(b.导入线索数,0) AS 新增线索数  " +
                    " FROM   " +
                    " kpi_allset allset  " +
                    "LEFT JOIN  " +
                    "(  " +
                    "  SELECT  " +
                    "    a.`许可`,b.`来源`,b.`品牌`,b.`级别`,  " +
                    "    COUNT(DISTINCT(a.`顾客编号`)) AS 散客线索数  " +
                    "  FROM  " +
                    "    `顾客进店记录表` a,  " +
                    "    lsh车型 b  " +
                    "  WHERE  " +
                    "    a.`是否首次到店` = '是'  " +
                    "  AND a.`顾客来源` = '散客'  " +
                    "  AND a.`进店日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                    "  AND a.`进店日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                    "  AND a.`意向车型A` = b.`车型编号`  " +
                    "  GROUP BY a.`许可`,b.`来源`,b.`品牌`,b.`级别`  " +
                    ") a ON a.`许可`=allset.许可_BMBS AND a.`来源`=allset.`来源` AND a.`品牌`=allset.`品牌` AND a.`级别`=allset.`级别`  " +
                    "LEFT JOIN  " +
                    "(  " +
                    "  SELECT  " +
                    "    a.`许可`,b.`来源`,b.`品牌`,b.`级别`,  " +
                    "    COUNT(DISTINCT(CONCAT(a.`许可`,a.`电话号码`))) AS 导入线索数  " +
                    "  FROM  " +
                    "    lsh导入名单 a,  " +
                    "    lsh车型 b  " +
                    "  WHERE  " +
                    "    a.`是否线索` = '是'  " +
                    "  AND a.`是否有效线索` = '是'  " +
                    "  AND a.`导入日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                    "  AND a.`导入日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                    "  AND a.`车型编号`=b.`车型编号`  " +
                    "  GROUP BY a.`许可`,b.`来源`,b.`品牌`,b.`级别`  " +
                    ") b ON b.`许可`=allset.许可_BMBS AND b.`来源`=allset.`来源` AND b.`品牌`=allset.`品牌` AND b.`级别`=allset.`级别`  " +
                    "LEFT JOIN  " +
                    "(  " +
                    "  SELECT  " +
                    "    a.`许可`,b.`来源`,b.`品牌`,b.`级别`,  " +
                    "    COUNT(DISTINCT(a.`顾客编号`)) AS 未战败线索数  " +
                    "  FROM  " +
                    "    `顾客进店记录表` a,  " +
                    "    `lsh车型` b,  " +
                    "    `顾客记录汇总` c  " +
                    "  WHERE  " +
                    "    a.`是否首次到店` = '否'  " +
                    "  AND a.`进店日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                    "  AND a.`进店日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                    "  AND a.`意向车型A` = b.`车型编号`  " +
                    "  AND a.`顾客编号` = c.`顾客编号`  " +
                    "  AND NOT EXISTS   " +
                    "  (  " +
                    "    SELECT  " +
                    "      *  " +
                    "    FROM  " +
                    "      `跟进记录表` follow  " +
                    "    WHERE  " +
                    "      follow.`许可` = c.`许可`  " +
                    "    AND follow.`电话号码` = c.`电话号码`  " +
                    "    AND follow.`跟进结果` = '新车战败'  " +
                    "    AND follow.`跟进日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                    "    AND follow.`跟进日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                    "  )  " +
                    "  GROUP BY a.`许可`,b.`来源`,b.`品牌`,b.`级别`  " +
                    ") c ON c.`许可`=allset.许可_BMBS AND c.`来源`=allset.`来源` AND c.`品牌`=allset.`品牌` AND c.`级别`=allset.`级别`";
    // 客户重新分配比例(OK,重新分配只有mgt操作的数据，现订现交需要反推逻辑)
    private String customerReassignmentRate =
            "SELECT  " +
                    "  allset.*,IFNULL(a.重新分配线索数,0) AS 重新分配线索数,IFNULL(b.进店次数,0) AS 进店次数  " +
                    "FROM  " +
                    "  kpi_allset allset  " +
                    "LEFT JOIN  " +
                    "(  " +
                    "  SELECT  " +
                    "    b.`许可`,c.`来源`,c.`品牌`,c.`级别`,COUNT(1) AS 重新分配线索数  " +
                    "  FROM  " +
                    "    custom_reassign_log a ,  " +
                    "    `顾客记录汇总` b,  " +
                    "    `lsh车型` c  " +
                    "  WHERE  " +
                    "    DATE_FORMAT(a.reassign_datetime , '%Y-%m-%d') >= DATE_FORMAT('2000-01-01' , '%Y-%m-%d')  " +
                    "  AND DATE_FORMAT(a.reassign_datetime , '%Y-%m-%d') <= DATE_FORMAT('2020-01-01' , '%Y-%m-%d')  " +
                    "  AND a.custom_code = b.`顾客编号`  " +
                    "  AND b.`意向车型A` = c.`车型编号`  " +
                    "  GROUP BY b.`许可`,c.`来源`,c.`品牌`,c.`级别`  " +
                    ") a ON a.`许可`=allset.许可_BMBS AND a.`来源`=allset.`来源` AND a.`品牌`=allset.`品牌` AND a.`级别`=allset.`级别`  " +
                    "LEFT JOIN  " +
                    "(  " +
                    "  SELECT  " +
                    "    a.`许可`,b.`来源`,b.`品牌`,b.`级别`,  " +
                    "    COUNT(DISTINCT(a.`顾客编号`)) AS 进店次数  " +
                    "  FROM  " +
                    "    `顾客进店记录表` a,  " +
                    "    lsh车型 b  " +
                    "  WHERE  " +
                    "  (a.`到店目的` IN ('看车比价','订单','交车')  " +
                    "  OR (a.`到店目的` = '办手续'))  " +
                    "  AND a.`进店日期` >= DATE_FORMAT('2000-01-01','%Y-%m-%d')   " +
                    "  AND a.`进店日期` <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  " +
                    "  AND a.`意向车型A` = b.`车型编号`  " +
                    "  GROUP BY a.`许可`,b.`来源`,b.`品牌`,b.`级别`  " +
                    ") b ON b.`许可`=allset.许可_BMBS AND b.`来源`=allset.`来源` AND b.`品牌`=allset.`品牌` AND b.`级别`=allset.`级别` ";
    // 试乘试驾率(OK)
    private String testDriveRate = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  b.来源, b.品牌, b.级别, " +
            "       试乘试驾数, 进店线索数 " +
            "   FROM lsh经销商表 a  " +
            "   LEFT JOIN (  " +
            "       SELECT 许可, 来源, 品牌, 级别, SUM(试乘试驾数) 试乘试驾数, SUM(IF(顾客编号 = 0 , 0, 1)) 进店线索数 " +
            "       FROM ( " +
            "	       SELECT a.许可, 来源, 品牌, b.级别, 1 AS 试乘试驾数, 0 顾客编号    " +
            "	       FROM 顾客进店记录表 a, LSH车型 b, `顾客试驾记录` c" +
            "	       WHERE a.进店编号 = c.进店编号  AND a.意向车型A = b.车型编号   " +
            "	           AND a.`进店日期` >= '2000-01-01' and a.`进店日期` <= '2020-01-01'    " +
            "              AND left(a.到店时间,2) not in ('00','01','02','03') " +
            "              AND (CASE WHEN 'MB' = '' THEN TRUE  ELSE B.品牌 = 'MB' END)  " +
            "          UNION ALL " +
            "	           SELECT a.许可, 来源, 品牌, 级别, 0 AS 试乘试驾数, 顾客编号    " +
            "	           FROM 顾客进店记录表 a, LSH车型 c    " +
            "	           WHERE a.`进店日期` >= '2000-01-01' and a.`进店日期` <= '2020-01-01'    " +
            "                 AND (a.到店目的 IN ('看车比价','订单') OR (a.到店目的 IN ('办手续','交车') AND a.是否订单='是')) " +
            "                AND left(a.到店时间,2) not in ('00','01','02','03') " +
            "               AND (CASE WHEN 'MB' = '' THEN TRUE ELSE C.品牌 = 'MB' END)  " +
            "                AND a.意向车型A = c.车型编号 " +
            "              GROUP BY  顾客编号 " +
            "       ) a GROUP BY 许可, 来源, 品牌, 级别 " +
            "  ) B ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'   ";

    // 报价率(OK)
    private String offerRate = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别, " +
            "    报价数, 进店线索数 " +
            "   FROM lsh经销商表 a  " +
            "   LEFT JOIN (  " +
            "       SELECT 许可, 来源, 品牌, 级别, SUM(报价数) 报价数, SUM(IF(顾客编号 = 0 , 0, 1)) 进店线索数 " +
            "       FROM ( " +
            "	       SELECT a.许可, 来源, b.品牌, b.级别, 1 AS 报价数, 0 顾客编号    " +
            "	       FROM 报价单表 a, LSH车型 b " +
            "	       WHERE a.车型编号 = b.车型编号   " +
            "	           AND date_format(a.`报价时间`,'%Y-%m-%d') >= '2000-01-01' and date_format(a.`报价时间`,'%Y-%m-%d') <= '2020-01-01'    " +
            "              AND (CASE WHEN 'MB' = '' THEN TRUE ELSE B.品牌 = 'MB' END)  " +
            "          GROUP BY 初始报价单号 " +
            "          UNION ALL " +
            "	           SELECT a.许可, 来源, c.品牌, c.级别, 0 AS 报价数, 顾客编号    " +
            "	           FROM 顾客进店记录表 a, LSH车型 c     " +
            "	           WHERE a.`进店日期` >= '2000-01-01' and a.`进店日期` <= '2020-01-01'    " +
            "                AND (a.到店目的 IN ('看车比价','订单') OR (a.到店目的 IN ('办手续','交车') AND a.是否订单='是'))  " +
            "                AND left(a.到店时间,2) not in ('00','01','02','03') " +
            "                AND (CASE WHEN 'MB' = '' THEN TRUE ELSE C.品牌 = 'MB' END)  " +
            "                AND a.意向车型A = c.车型编号 " +
            "              GROUP BY  顾客编号 " +
            "       ) a GROUP BY 许可, 来源, 品牌, 级别 " +
            "  ) B ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'   ";

    // 订单转化率(OK)
    private String orderConversionRate = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别, " +
            "   订单数, 进店线索数 " +
            "   FROM lsh经销商表 a  " +
            "   LEFT JOIN (  " +
            "       SELECT 许可, 来源, 品牌, 级别, SUM(订单数) 订单数, SUM(IF(顾客编号 = 0 , 0, 1)) 进店线索数 " +
            "       FROM ( " +
            "	       SELECT a.许可, 来源, b.品牌, b.级别, " +
            "              CASE " +
            "                 WHEN DATE_FORMAT(a.订单审批通过日期,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.订单审批通过日期,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND  " +
            "                      DATE_FORMAT(a.订单失销日期,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.订单失销日期,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0 " +
            "                 WHEN DATE_FORMAT(a.订单审批通过日期,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.订单审批通过日期,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1 " +
            "                 WHEN DATE_FORMAT(a.订单失销日期,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.订单失销日期,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1 " +
            "              END 订单数, 0 顾客编号 " +
            "	       FROM VW_ORDER_QUOTATION a, LSH车型 b " +
            "	       WHERE a.车型编号 = b.车型编号   " +
            "	           AND ((date_format(a.`订单审批通过日期`,'%Y-%m-%d') >= '2000-01-01' and date_format(a.`订单审批通过日期`,'%Y-%m-%d') <= '2020-01-01')    " +
            "                OR (date_format(a.`订单失销日期`,'%Y-%m-%d') >= '2000-01-01' and date_format(a.`订单失销日期`,'%Y-%m-%d') <= '2020-01-01')) " +
            "              AND (CASE WHEN 'MB' = '' THEN TRUE ELSE B.品牌 = 'MB' END)  " +
            "          UNION ALL " +
            "	           SELECT a.许可, 来源, c.品牌, c.级别, 0 AS 订单数, 顾客编号    " +
            "	           FROM 顾客进店记录表 a,  LSH车型 c     " +
            "	           WHERE a.`进店日期` >= '2000-01-01' and a.`进店日期` <= '2020-01-01'    " +
            "                AND (a.到店目的 IN ('看车比价','订单') OR (a.到店目的 IN ('办手续','交车') AND a.是否订单='是'))  " +
            "                AND left(a.到店时间,2) not in ('00','01','02','03') " +
            "                AND (CASE WHEN 'MB' = '' THEN TRUE ELSE C.品牌 = 'MB' END)  " +
            "                AND a.意向车型A = c.车型编号 " +
            "              GROUP BY  顾客编号 " +
            "       ) a GROUP BY 许可, 来源, 品牌, 级别 " +
            "  ) B ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'   ";

    // 成交率(OK)
    private String salesConversionRate = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别, " +
            "   新车销量, 进店线索数 " +
            "   FROM lsh经销商表 a  " +
            "   LEFT JOIN (  " +
            "       SELECT 许可, 来源, 品牌, 级别, SUM(新车销量) 新车销量, SUM(IF(顾客编号 = 0 , 0, 1)) 进店线索数 " +
            "       FROM ( " +
            "	       SELECT a.许可, 来源, b.品牌, b.级别, " +
            "              CASE " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND  " +
            "                      DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0 " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1 " +
            "                 WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1 " +
            "              END 新车销量, 0 顾客编号 " +
            "	        FROM vw_retailed_quotation a, LSH车型 b  " +
            "	       WHERE a.车型编号 = b.车型编号   " +
            "	           AND (date_format(a.`开票时间`,'%Y-%m-%d') >= '2000-01-01' and date_format(a.`开票时间`,'%Y-%m-%d') <= '2020-01-01'    " +
            "                OR date_format(a.`退票时间`,'%Y-%m-%d') >= '2000-01-01' and date_format(a.`退票时间`,'%Y-%m-%d') <= '2020-01-01') " +
            "              AND (CASE WHEN 'MB' = '' THEN TRUE ELSE B.品牌 = 'MB' END)  " +
            "          UNION ALL " +
            "	           SELECT a.许可, 来源, c.品牌, c.级别, 0 AS 新车销量, 顾客编号    " +
            "	           FROM 顾客进店记录表 a, LSH车型 c     " +
            "	           WHERE a.`进店日期` >= '2000-01-01' and a.`进店日期` <= '2020-01-01'    " +
            "                AND (a.到店目的 IN ('看车比价','订单') OR (a.到店目的 IN ('办手续','交车') AND a.是否订单='是')) " +
            "                AND left(a.到店时间,2) not in ('00','01','02','03') " +
            "                AND (CASE WHEN 'MB' = '' THEN TRUE ELSE C.品牌 = 'MB' END)  " +
            "                AND a.意向车型A = c.车型编号 " +
            "              GROUP BY  a.许可, 来源, c.品牌, 级别, 新车销量, 顾客编号 " +
            "       ) a GROUP BY 许可, 来源, 品牌, 级别 " +
            "  ) B ON a.许可_BMBS = b.许可  where a.`是否测试` = '否'  ";
    // 展厅客流量LSH(OK)
    private String showroomTraffic = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别, " +
            "   进店线索数 " +
            "   FROM lsh经销商表 a  " +
            "   LEFT JOIN (  " +
            "       SELECT 许可, 来源, 品牌, 级别, COUNT(顾客编号) 进店线索数 " +
            "       FROM ( " +
            "	        SELECT a.许可, 来源, c.品牌, c.级别,  a.顾客编号    " +
            "	        FROM 顾客进店记录表 a LEFT JOIN 报价单表 ON a.报价编号 = 报价单表.报价编号 " +
            "                               LEFT JOIN 报价订单表 ON  报价单表.订单编号 = 报价订单表.订单编号," +
            "           LSH车型 c     " +
            "	        WHERE a.`进店日期` >= '2000-01-01' and a.`进店日期` <= '2020-01-01'    " +
            "             AND left(a.到店时间,2) not in ('00','01','02','03') " +
            "             AND a.意向车型A = c.车型编号 " +
            "             AND (" +
            "               a.到店目的 IN ('看车比价','订单')  " +
            "               OR (a.到店目的 IN ('办手续','交车') " +
            "               AND a.是否订单='是')" +
            "             ) " +
            "             AND (CASE WHEN 'MB' = '' THEN TRUE ELSE C.品牌 = 'MB' END)  " +
            "           GROUP BY  a.顾客编号 " +
            "       ) a GROUP BY 许可, 来源, 品牌, 级别    " +
            "   ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'   ";

    // 新增净订单数(OK)
    private String OrderIn_take = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, e.品牌, e.级别,  " +
            "        订单数 as 新增订单数   " +
            " from lsh经销商表 a     " +
            " LEFT OUTER JOIN (     " +
            "            select sum(CASE  " +
            "                  WHEN date_format(b.订单审批通过日期,'%Y-%m-%d') >= '2000-01-01'  and date_format(b.订单审批通过日期,'%Y-%m-%d')  <= '2020-01-01'   " +
            "                       AND  date_format(b.订单失销日期,'%Y-%m-%d') >= '2000-01-01' and date_format(b.订单失销日期,'%Y-%m-%d')  <= '2020-01-01'   " +
            "                  THEN 0  " +
            "                  WHEN date_format(b.订单审批通过日期,'%Y-%m-%d') >= '2000-01-01' and date_format(b.订单审批通过日期,'%Y-%m-%d') <='2020-01-01' THEN 1  " +
            "                  WHEN date_format(b.订单失销日期,'%Y-%m-%d') >= '2000-01-01'  and  date_format(b.订单失销日期,'%Y-%m-%d') <= '2020-01-01' THEN -1  " +
            "                  END) as 订单数,  " +
            "                 b.许可  , 来源, d.品牌,d.级别            " +
            "           from  VW_ORDER_QUOTATION  b          " +
            "           left join LSH车型 d on b.`车型编号` = d.车型编号         " +
            "           where  ((date_format(B.订单审批通过日期,'%Y-%m-%d') <= '2020-01-01'  AND date_format(b.订单审批通过日期,'%Y-%m-%d') >= '2000-01-01')     " +
            "                        OR ((date_format(b.订单失销日期,'%Y-%m-%d') <= '2020-01-01'  AND date_format(b.订单失销日期,'%Y-%m-%d') >= '2000-01-01')))           " +
            "                and (case when 'MB' = '' then true else d.品牌 = 'MB' end)  " +
            "            group by b.许可 , 来源, d.品牌,d.级别       " +
            "            ) e on a.许可_BMBS = e.许可  where a.`是否测试` = '否'    ";

    // 新增交车数(OK)
    private String delivery = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别, " +
            "   交车数 " +
            "   FROM lsh经销商表 a  " +
            "   LEFT JOIN (  " +
            "       SELECT 许可, 来源, 品牌, 级别, SUM(交车数) 交车数 " +
            "       FROM ( " +
            "	        SELECT a.许可, 来源, c.品牌, c.级别, 1 AS 交车数    " +
            "	        FROM vw_retailed_quotation a LEFT JOIN 报价交车表 b ON a.交车 = b.编号, LSH车型 c     " +
            "	        WHERE date_format(b.`交车日期`,'%Y-%m-%d') >= '2000-01-01' and date_format(b.`交车日期`,'%Y-%m-%d') <= '2020-01-01'    " +
            "	          AND b.退车日期 is null    " +
            "	          AND a.`车型编号` = C.车型编号 " +
            "             AND  (case when 'MB' = '' then true else c.品牌 = 'MB' end)      " +
            "       ) a GROUP BY 许可, 来源, 品牌, 级别    " +
            "   ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否'   ";

    // 库存周转天数(OK)
    private String stockTurnover = " select z.*,周转库存, 前三个月平均销量 前三个月平均销量    " +
            " from kpi_allset z   " +
            "  left join (     " +
            "        select 30 * count(1) 周转库存, b.所属经销商 as 许可,来源,品牌,级别     " +
            "        from  LSH车型 a, LSH库存表 b " +
            "        WHERE a.车型编号 = b.车型编号 AND b.批发日期 <= '2020-01-01' and b.is_delete = 0 and b.车架号 not like 'IRIS%'    " +
            "         and (case when 'MB' = '' then true else a.品牌 = 'MB' end)   " +
            "         AND NOT EXISTS (" +
            "                SELECT c.库存编号 " +
            "                 FROM vw_stock_quotation c " +
            "                WHERE " +
            "                 (" +
            "                   (c.开票时间 <= '2020-01-01 23:59:59' and c.退票时间 IS NULL)" +
            "                   or (c.开票时间 <= '2020-01-01 23:59:59' and c.退票时间 < c.开票时间)" +
            "                 )   " +
            "                   AND c.库存编号 = b.库存编号" +
            "         )      " +
            "        group by b.所属经销商,来源,品牌,级别      " +
            "      ) a  on z.许可_BMBS = a.许可  AND a.`来源`=z.`来源` AND a.`品牌`=z.`品牌` AND a.`级别`=z.`级别`   " +
            "  left join (     " +
            //-- 过去三个月已零售
            "        select sum(CASE    " +
            "                    WHEN date_format(a.开票时间,'%Y-%m-%d') >= CONCAT(DATE_FORMAT(DATE_SUB('2020-01-01',INTERVAL 3 MONTH),'%Y-%m'),'-01')       " +
            "                      and date_format(a.开票时间,'%Y-%m-%d')  <= DATE_FORMAT(last_day(DATE_SUB('2020-01-01',INTERVAL 1 MONTH)),'%Y-%m-%d')    " +
            "                      AND  date_format(a.退票时间,'%Y-%m-%d') >= CONCAT(DATE_FORMAT(DATE_SUB('2020-01-01',INTERVAL 3 MONTH),'%Y-%m'),'-01')     " +
            "                      and date_format(a.退票时间,'%Y-%m-%d')  <= DATE_FORMAT(last_day(DATE_SUB('2020-01-01',INTERVAL 1 MONTH)),'%Y-%m-%d')    " +
            "                      AND a.退票时间 > a.开票时间 THEN 0   " +
            "                    WHEN date_format(a.开票时间,'%Y-%m-%d') >= CONCAT(DATE_FORMAT(DATE_SUB('2020-01-01',INTERVAL 3 MONTH),'%Y-%m'),'-01')  " +
            "                      and date_format(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT(last_day(DATE_SUB('2020-01-01',INTERVAL 1 MONTH)),'%Y-%m-%d') THEN 1   " +
            "                    WHEN date_format(a.退票时间,'%Y-%m-%d') >= CONCAT(DATE_FORMAT(DATE_SUB('2020-01-01',INTERVAL 3 MONTH),'%Y-%m'),'-01')       " +
            "                      and  date_format(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT(last_day(DATE_SUB('2020-01-01',INTERVAL 1 MONTH)),'%Y-%m-%d') THEN -1   " +
            "                    END) / 3 as '前三个月平均销量', a.许可 ,来源,e.品牌,e.级别     " +
            "         from vw_retailed_quotation a          " +
            "         join LSH车型 e on a.车型编号 = e.车型编号   " +
            "         join 报价订单表 c ON a.订单编号 = c.订单编号  " +
            "         where  ((date_format(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT(last_day(DATE_SUB('2020-01-01',INTERVAL 1 MONTH)),'%Y-%m-%d')  AND date_format(a.开票时间,'%Y-%m-%d') >= CONCAT(DATE_FORMAT(DATE_SUB('2020-01-01',INTERVAL 3 MONTH),'%Y-%m'),'-01'))      " +
            "                OR (date_format(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT(last_day(DATE_SUB('2020-01-01',INTERVAL 1 MONTH)),'%Y-%m-%d')  AND date_format(a.退票时间,'%Y-%m-%d') >= CONCAT(DATE_FORMAT(DATE_SUB('2020-01-01',INTERVAL 3 MONTH),'%Y-%m'),'-01')))  " +
            "            AND a.订单编号 = c.订单编号  " +
            "            AND c.销售类型 not like 'B%'  " +
            "            AND c.`销售类型`not like 'I%'      " +
            "            and a.车辆零售状态 = '已卖'      " +
            "            and (case when 'MB' = '' then true else e.品牌 = 'MB' end)       " +
            "          group by a.许可,来源,e.品牌,e.级别      " +
            "    ) b on z.许可_BMBS = b.许可  AND B.`来源`=z.`来源` AND B.`品牌`=z.`品牌` AND B.`级别`=z.`级别`";


    // 其他业务收入毛利率不含二手车续保(OK)   测试完成  偏大 0.6%   经销商计算规则少上牌服务费，其他服务费，新保手续费，五星延保毛利
    private String otherIncomeGPPercent = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
            "   a.salesCode, a.来源,a.品牌, a.级别,     " +
            "   SUM(VEHICLE_OTHER_INCOME.TYPE * (车辆销售价未税 - 消费税未税) ) AS 新车外部销售收入,  " +
            "   SUM(VEHICLE_OTHER_INCOME.TYPE * (精品销售毛利未税 + 其他服务费未税 + 上牌服务费未税 + 置换评估费未税 +  金融服务费未税 + 金融手续费未税 + 保险手续费未税)) + " +
            "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust,0)/1.17  " +
            "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.insurance_poundage_adjust,0)/1.17 " +
            "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust,0)/1.17 AS 其他收入," +
            "   IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.finance_poundage_adjust,0)/1.17  " +
            "   + IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.insurance_poundage_adjust,0)/1.17 " +
            "   - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust,0)/1.17 AS 品牌调整值 " +
            "   FROM kpi_allset a " +
            "   LEFT JOIN (  " +
            "        SELECT a.许可, d.来源, d.品牌,d.级别,  " +
            "               CASE  " +
            "                  WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND   " +
            "                       DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0  " +
            "                  WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1  " +
            "                  WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1  " +
            "               END TYPE,  " +
            "               IFNULL(a.折后价,0)/1.17 AS 车辆销售价未税,   " +
            "               IFNULL(a.消费税,0) / 1.17 AS 消费税未税,     " +
            "            	IFNULL(hh.上牌服务费, 0) / 1.06 AS 上牌服务费未税,   " +
            "           	IFNULL(h.其他服务费,0) / 1.06 AS 其他服务费未税,      " +
            "           	IFNULL(i.金融服务费,0) / 1.06 AS 金融服务费未税,     " +
            "           	IFNULL(i.金融手续费,0) / 1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 AS 金融手续费未税,      " +
            "           	IFNULL(j.交强险手续费,0) / 1.17 + IFNULL(j.商业险手续费,0) / 1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0)/1.17 AS 保险手续费未税,    " +
            "           	IFNULL(k.置换评估费,0) / 1.17 AS 置换评估费未税,      " +
            "               IFNULL(ll.精品销售毛利, 0) / 1.17 - IFNULL(VEHICLE_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust, 0)/1.17 AS 精品销售毛利未税    " +
            "        FROM vw_retailed_quotation a  " +
            "        LEFT JOIN (      " +
            "            SELECT a.编号, SUM(IFNULL(a.`折后价` * 数量, 0)) - SUM(IFNULL(a.成本 * 数量, 0)) AS 精品销售毛利  " +
            "            FROM 报价精品详细表 a WHERE 折后价 > 0 GROUP BY a.`编号`     " +
            "        ) ll ON ll.编号 = a.精品      " +
            "        LEFT JOIN (      " +
            "            SELECT a.费用编号, SUM(b.费用折后价) AS 其他服务费      " +
            "            FROM 报价费用表 a JOIN 报价费用详细表 b ON b.费用编号 = a.费用编号     " +
            "            WHERE b.费用名称 = '其他服务费'      " +
            "            GROUP BY a.费用编号      " +
            "        ) h ON h.费用编号 = a.费用编号    " +
            "        LEFT JOIN (      " +
            "            SELECT a.费用编号, SUM(b.费用折后价) AS 上牌服务费      " +
            "            FROM 报价费用表 a  JOIN 报价费用详细表 b ON b.费用编号 = a.费用编号    " +
            "            WHERE b.费用名称 = '上牌服务费'      " +
            "            GROUP BY a.费用编号      " +
            "        ) hh ON hh.费用编号 = a.费用编号    " +
            "        LEFT JOIN 报价金融表 i ON i.金融编号 = a.金融      " +
            "        LEFT JOIN 报价保险表 j ON j.保险编号 = a.保险编号    " +
            "        LEFT JOIN 报价置换表 k ON k.置换编号 = a.置换编号      " +
            "        LEFT JOIN (  " +
            "            SELECT PERMIT, STOCK_CODE,   " +
            "                   SUM(finance_poundage_adjust) finance_poundage_adjust, " +
            "                   SUM(insurance_poundage_adjust) insurance_poundage_adjust, " +
            "                   SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
            "            FROM INCOME_ADJUSTMENT   " +
            "            WHERE TYPE = 1  " +
            "            GROUP BY PERMIT, STOCK_CODE " +
            "        ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.PERMIT =a.许可 AND VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号  " +
            "        ,LSH车型 d    " +
            "        WHERE  ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "            OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01'))) " +
            "           AND (CASE WHEN 'MB' = '' THEN TRUE ELSE d.品牌 = 'MB' END)  " +
            "           AND a.车型编号 = d.车型编号    " +
            "    ) VEHICLE_OTHER_INCOME ON a.许可_BMBS = VEHICLE_OTHER_INCOME.许可 AND a.来源 = VEHICLE_OTHER_INCOME.来源 AND a.品牌 = VEHICLE_OTHER_INCOME.品牌 AND  a.级别 = VEHICLE_OTHER_INCOME.级别  " +
            "    LEFT JOIN (" +
            "         SELECT PERMIT, 来源, 品牌, 级别,   " +
            "                SUM(finance_poundage_adjust) finance_poundage_adjust, " +
            "                SUM(insurance_poundage_adjust) insurance_poundage_adjust, " +
            "                SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
            "         FROM INCOME_ADJUSTMENT, LSH车型   " +
            "         WHERE INCOME_ADJUSTMENT.TYPE = 2 AND IS_GIVE = 0 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
            "               AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
            "               AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "               AND '2000-01-01' <> '2020-01-01' " +
            "         GROUP BY PERMIT, 来源, 品牌, 级别  " +
            "    ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
            "    LEFT JOIN (" +
            "         SELECT PERMIT, 来源, 品牌,  " +
            "                SUM(finance_poundage_adjust) finance_poundage_adjust, " +
            "                SUM(insurance_poundage_adjust) insurance_poundage_adjust, " +
            "                SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
            "         FROM INCOME_ADJUSTMENT, LSH车型   " +
            "         WHERE INCOME_ADJUSTMENT.TYPE = 2 AND IS_GIVE = 0 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
            "           AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
            "           AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "           AND '2000-01-01' <> '2020-01-01' " +
            "         GROUP BY PERMIT, 来源, 品牌  " +
            "    ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
            " WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
            " GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";

    // LSH5星延保渗透率 + LSH5星延保毛利率 (OK) (CHECKED)
    private String LSH_Extend_Warranty = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
            " a.salesCode, a.来源,a.品牌, a.级别, " +
            " SUM(VEHICLE_FIVE_STAR_EXTEND.TYPE * 五星延保销售台数) 五星延保销售台数, " +
            " SUM(VEHICLE_FIVE_STAR_EXTEND.TYPE * 延保销售毛利) - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 延保销售毛利, " +
            " SUM(VEHICLE_FIVE_STAR_EXTEND.TYPE * 延保销售毛利) - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 延保销售毛利, " +
            " SUM(VEHICLE_FIVE_STAR_EXTEND.TYPE * 新车销量) 新车销量, " +
            " SUM(VEHICLE_FIVE_STAR_EXTEND.TYPE * (VEHICLE_FIVE_STAR_EXTEND.车辆销售价未税 - VEHICLE_FIVE_STAR_EXTEND.消费税未税)) 新车外部销售收入, " +
            " SUM(VEHICLE_FIVE_STAR_EXTEND.TYPE * 延保销售营业额) 延保销售营业额, " +
            " 0 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 品牌五星延保毛利成本调整 " +
            " FROM kpi_allset a " +
            " LEFT JOIN ( " +
            "           SELECT  a.许可, d.来源, d.品牌, d.级别,   " +
            "               CASE  " +
            "                   WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND   " +
            "                        DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') AND a.退票时间>a.开票时间 THEN 0  " +
            "                   WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1  " +
            "                   WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1  " +
            "               END TYPE , " +
            "               IFNULL(IF(LEFT(e.销售类型,1) IN ('B', 'D', 'I', 'P'), 0, a.折后价),0) /1.17 AS 车辆销售价未税,      " +
            "               IFNULL(IF(LEFT(e.销售类型,1) IN ('B', 'D', 'I', 'P'), 0,a.消费税),0) / 1.17 AS 消费税未税,      " +
            "               IFNULL(b.延保销售毛利, 0)/1.17 - IFNULL(VEHICLE_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 延保销售毛利,  " +
            "               IFNULL(b.延保销售营业额, 0)/1.17  延保销售营业额,  " +
            "               IF(LEFT(e.订单状态,1) IN ('B','I') , 0 , 1) 新车销量 , " +
            "               IF(B.延保原价 IS NOT NULL, 1, 0) 五星延保销售台数  " +
            "          FROM vw_retailed_quotation a " +
            "          LEFT JOIN (  " +
            "              SELECT PERMIT, STOCK_CODE,   " +
            "                     SUM(five_stars_extend_cost_adjust) five_stars_extend_cost_adjust " +
            "              FROM INCOME_ADJUSTMENT   " +
            "              WHERE TYPE = 1  AND IS_GIVE = 0 " +
            "              GROUP BY PERMIT, STOCK_CODE " +
            "          ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.PERMIT =a.许可 AND VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号  " +
            "          LEFT JOIN (        " +
            "		      SELECT 延保编号, IFNULL(延保原价,0) 延保原价 ,(IFNULL(延保折后价,0) - IFNULL(延保原价,0)) AS 延保销售毛利        " +
            "                    IFNULL(延保折后价,0) AS 延保销售营业额,    " +
            "        	  FROM 报价五星延保  WHERE 延保折后价 > 0  " +
            "          ) b ON b.延保编号 = a.延保编号,         " +
            "          LSH车型 d,  报价订单表 e " +
            "          WHERE a.车型编号 = d.车型编号 AND a.订单编号 = e.订单编号 " +
            "            AND ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "	          OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01'))) " +
            "   ) VEHICLE_FIVE_STAR_EXTEND ON a.许可_BMBS = VEHICLE_FIVE_STAR_EXTEND.许可 AND a.来源 = VEHICLE_FIVE_STAR_EXTEND.来源 AND a.品牌 = VEHICLE_FIVE_STAR_EXTEND.品牌 AND  a.级别 = VEHICLE_FIVE_STAR_EXTEND.级别" +
            "   LEFT JOIN (" +
            "       SELECT PERMIT, 来源, 品牌, 级别,   " +
            "              SUM(five_stars_extend_cost_adjust) five_stars_extend_cost_adjust " +
            "       FROM INCOME_ADJUSTMENT, LSH车型   " +
            "       WHERE INCOME_ADJUSTMENT.TYPE = 2 AND IS_GIVE = 0 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
            "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
            "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "             AND '2000-01-01' <> '2020-01-01' " +
            "       GROUP BY PERMIT, 来源, 品牌, 级别  " +
            "   ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
            "   LEFT JOIN (" +
            "       SELECT PERMIT, 来源, 品牌,  " +
            "              SUM(five_stars_extend_cost_adjust) five_stars_extend_cost_adjust " +
            "       FROM INCOME_ADJUSTMENT, LSH车型   " +
            "       WHERE INCOME_ADJUSTMENT.TYPE = 2 AND IS_GIVE = 0 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
            "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
            "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "             AND '2000-01-01' <> '2020-01-01' " +
            "       GROUP BY PERMIT, 来源, 品牌  " +
            "   ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
            " WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
            " GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";

    // 金融渗透率(OK)   测试调整完成  需 待确认  03是不是 战败。4-28 -16:21 (CHECKED)
    @Deprecated
    private String financingPenetrationRate = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode,  来源, b.品牌, b.级别, " +
            " 零售金融台数, 新车销量 " +
            " FROM LSH经销商表 a LEFT JOIN ( " +
            "     SELECT a.许可, 来源, a.品牌, a.级别, " +
            "            SUM(TYPE * 零售金融台数) 零售金融台数, " +
            "            SUM(TYPE * 新车销量) 新车销量 " +
            "     FROM ( " +
            "           SELECT  a.许可, d.来源, d.品牌, d.级别,  " +
            "               CASE  " +
            "                   WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND   " +
            "                        DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') AND a.退票时间>a.开票时间 THEN 0  " +
            "                   WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1  " +
            "                   WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1  " +
            "               END TYPE , " +
            "               1 新车销量 , " +
            "              IF( B.金融机构 IS NOT NULL AND B.贷款额 > 0 AND C.状态 IS NOT NULL and C.状态 <>'04'and  C.状态 <>'09' ,1,0)  零售金融台数  " +
            "          FROM vw_retailed_quotation a " +
            "          LEFT  JOIN 报价金融表 b ON a.`金融` = b.`金融编号` " +
            "          LEFT JOIN LSH金融申请 c ON a.初始报价单号 = c.初始报价单号 AND C.状态 NOT IN ('04','09'), " +
            "          LSH车型 d " +
            "          WHERE a.车型编号 = d.车型编号 " +
            "            AND ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "	          OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01'))) " +
            "            AND (CASE WHEN 'MB' = '' THEN TRUE ELSE d.品牌 = 'MB' END)  " +
            "   ) a GROUP BY a.许可, 来源, a.品牌, a.级别 " +
            " ) b ON a.许可_BMBS = b.许可 where a.`是否测试` = '否' ";

    // 金融申请率(KO)
    private String financingApplicationRate = "SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, a.salesCode, b.来源,b.品牌, b.级别,    " +
            "   b.订单金融台数 as  '订单金融台数',    " +
            "	b.订单数 as  '订单数'    " +
            "from lsh经销商表 a  " +
            "left join (  " +
            "    SELECT b.许可, d.来源,d.品牌, d.级别, " +
            "       COUNT(1) 订单数, " +
            "       SUM(IF(c.金融手续费 is not null  && `贷款额` > 0, 1, 0)) 订单金融台数 " +
            "    from  VW_ORDER_QUOTATION  b          " +
            "    LEFT JOIN 报价金融表 c ON B.金融 = c.金融编号 " +
            "    left join LSH车型 d on b.`车型编号` = d.车型编号         " +
            "    where  ((date_format(B.订单审批通过日期,'%Y-%m-%d') <= '2020-01-01'  AND date_format(b.订单审批通过日期,'%Y-%m-%d') >= '2000-01-01')     " +
            "        OR ((date_format(b.订单失销日期,'%Y-%m-%d') <= '2020-01-01'  AND date_format(b.订单失销日期,'%Y-%m-%d') >= '2000-01-01')))           " +
            "        and (case when 'MB' = '' then true else d.品牌 = 'MB' end)  " +
            "    group by b.许可 , 来源, d.品牌,d.级别       " +
            " ) b on a.`许可_BMBS` = b.许可 " +
            " where a.`是否测试` = '否' ";

    // 金融保险业务毛利率不含续保(OK)
    private String FandI_Income_GP_Percent = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
            " a.salesCode, a.来源,a.品牌, a.级别,  " +
            " SUM(VEHICLE_FINANCE_INSURANCE.TYPE * (车辆销售价未税 - 消费税未税)) 新车外部销售收入, " +
            " SUM(VEHICLE_FINANCE_INSURANCE.TYPE * (金融费用 + 保险费用)) + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust, 0) + IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0) 金融保险费用," +
            " IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.finance_poundage_adjust, 0) /1.17+ IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0)/1.17 品牌调整值   " +
            " FROM kpi_allset a  " +
            " LEFT JOIN (" +
            "	       SELECT  a.许可, c.来源, c.品牌, c.级别, " +
            "              CASE " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND  " +
            "                      DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0 " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1 " +
            "                 WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1 " +
            "              END TYPE, " +
            "              IFNULL(a.折后价,0)/1.17 AS 车辆销售价未税,      " +
            "              IFNULL(a.消费税,0) / 1.17 AS 消费税未税,      " +
            "              ifnull(b.金融手续费, 0) / 1.17  + ifnull(b.金融服务费, 0) / 1.06 + ifnull(VEHICLE_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 金融费用, " +
            "              (ifnull(d.交强险手续费, 0) + ifnull(d.商业险手续费, 0) + ifnull(VEHICLE_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0)) / 1.17 保险费用 " +
            "          FROM vw_retailed_quotation a " +
            "	       LEFT JOIN 报价金融表 b ON  b.金融编号 = a.金融 " +
            "          LEFT JOIN 报价保险表 d ON d.保险编号 = a.保险编号 " +
            "          LEFT JOIN (  " +
            "               SELECT PERMIT, STOCK_CODE,   " +
            "                      SUM(finance_poundage_adjust) finance_poundage_adjust, " +
            "                      SUM(insurance_poundage_adjust) insurance_poundage_adjust   " +
            "               FROM INCOME_ADJUSTMENT   " +
            "               WHERE TYPE = 1  " +
            "               GROUP BY PERMIT, STOCK_CODE " +
            "          ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.PERMIT =a.许可 AND VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号,  " +
            "          LSH车型 c  " +
            "          WHERE a.车型编号 = c.车型编号 " +
            "            AND ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "	          OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01'))) " +
            "            AND (CASE WHEN 'MB' = '' THEN TRUE ELSE c.品牌 = 'MB' END)  " +
            "   ) VEHICLE_FINANCE_INSURANCE ON a.许可_BMBS = VEHICLE_FINANCE_INSURANCE.许可 AND a.来源 = VEHICLE_FINANCE_INSURANCE.来源 AND a.品牌 = VEHICLE_FINANCE_INSURANCE.品牌 AND  a.级别 = VEHICLE_FINANCE_INSURANCE.级别 " +
            "   LEFT JOIN (" +
            "        SELECT PERMIT, 来源, 品牌, 级别,   " +
            "               SUM(finance_poundage_adjust) finance_poundage_adjust, " +
            "               SUM(insurance_poundage_adjust) insurance_poundage_adjust   " +
            "        FROM INCOME_ADJUSTMENT, LSH车型   " +
            "        WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
            "              AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
            "              AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "              AND '2000-01-01' <> '2020-01-01' " +
            "              GROUP BY PERMIT, 来源, 品牌, 级别  " +
            "   ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
            "   LEFT JOIN (" +
            "        SELECT PERMIT, 来源, 品牌,   " +
            "               SUM(finance_poundage_adjust) finance_poundage_adjust, " +
            "               SUM(insurance_poundage_adjust) insurance_poundage_adjust   " +
            "        FROM INCOME_ADJUSTMENT, LSH车型   " +
            "        WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
            "              AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
            "              AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "              AND '2000-01-01' <> '2020-01-01' " +
            "        GROUP BY PERMIT, 来源, 品牌  " +
            "   ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
            " WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
            " GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";

    // 后市场业务毛利率(OK)(CHECKED)
    private String afterMarketGPPercent = " SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
            " a.salesCode, a.来源,a.品牌, a.级别,   " +
            " SUM(VEHICLE_AFTERMARKET_BUSINESS.TYPE * (车辆销售价未税 - 消费税未税)) 新车外部销售收入, " +
            " SUM(VEHICLE_AFTERMARKET_BUSINESS.TYPE * 后市场业务费用)  - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust, 0)/1.17  后市场业务费用, " +
            " 0 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust, 0)/1.17 品牌后市场产品成本调整   " +
            " FROM kpi_allset a  " +
            " LEFT JOIN (" +
            "	 SELECT  a.许可, C.来源, c.品牌, c.级别, " +
            "        CASE " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND  " +
            "                      DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0 " +
            "                 WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1 " +
            "                 WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1 " +
            "        END TYPE, " +
            "        IFNULL(a.折后价,0)/1.17 AS 车辆销售价未税,      " +
            "        IFNULL(a.消费税,0) / 1.17 AS 消费税未税,      " +
            "        (IFNULL(b.后市场产品营业额,0)/1.17 - IFNULL(b.后市场产品成本,0)/1.17 + IFNULL(d.后市场业务费用,0)/1.06 - IFNULL(VEHICLE_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust, 0)/ 1.17)  后市场业务费用 " +
            "    FROM vw_retailed_quotation a " +
            "    LEFT JOIN ( " +
            "		  SELECT 编号, SUM(IFNULL(`折后价` * 数量, 0)) 后市场产品营业额, SUM(IFNULL(成本* 数量,0)) 后市场产品成本     " +
            "	      FROM 报价精品详细表 a WHERE 折后价 > 0 GROUP BY a.`编号`     " +
            "	 ) b ON b.编号 = a.精品 " +
            "    LEFT JOIN (      " +
            "         SELECT 费用编号, SUM(费用折后价) AS 后市场业务费用      " +
            "	      FROM 报价费用详细表      " +
            "	      WHERE 费用名称 = '其他服务费'  ||  费用名称 = '上牌服务费'    " +
            "	      GROUP BY 费用编号      " +
            "	 ) d ON d.费用编号 = a.费用编号    " +
            "    LEFT JOIN (  " +
            "         SELECT PERMIT, STOCK_CODE,   " +
            "                SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
            "         FROM INCOME_ADJUSTMENT   " +
            "         WHERE TYPE = 1  " +
            "         GROUP BY PERMIT, STOCK_CODE " +
            "    ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.PERMIT =a.许可 AND VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号  " +
            "    ,LSH车型 c  " +
            "    WHERE a.车型编号 = c.车型编号 " +
            "      AND ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')    " +
            "	   OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01'))) " +
            "      AND (CASE WHEN 'MB' = '' THEN TRUE ELSE c.品牌 = 'MB' END)  " +
            "   ) VEHICLE_AFTERMARKET_BUSINESS ON a.许可_BMBS = VEHICLE_AFTERMARKET_BUSINESS.许可 AND a.来源 = VEHICLE_AFTERMARKET_BUSINESS.来源 AND a.品牌 = VEHICLE_AFTERMARKET_BUSINESS.品牌 AND  a.级别 = VEHICLE_AFTERMARKET_BUSINESS.级别 " +
            "   LEFT JOIN (" +
            "       SELECT PERMIT, 来源, 品牌, 级别,   " +
            "              SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
            "       FROM INCOME_ADJUSTMENT, LSH车型   " +
            "       WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
            "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
            "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "             AND '2000-01-01' <> '2020-01-01' " +
            "       GROUP BY PERMIT, 来源, 品牌, 级别  " +
            "   ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
            "   LEFT JOIN (" +
            "       SELECT PERMIT, 来源, 品牌,  " +
            "              SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust " +
            "       FROM INCOME_ADJUSTMENT, LSH车型   " +
            "       WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
            "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
            "             AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
            "             AND '2000-01-01' <> '2020-01-01' " +
            "       GROUP BY PERMIT, 来源, 品牌  " +
            "   ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
            " WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
            " GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";
}
